Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Change Fill Color If Cell Value Is Greater Than Other Cell Value

So I'm trying to write a macro that will change the fill color of the cell in a column exceeds the value of another cell, here is what I have so far.

For Each Cell In Range("G2", Range("G65536").End(xlUp))
If Cell.Value > Sheets("Settings").Range("C2").Value Then

End If

How can I make it change the fill color of the cell in that column that exceeds the value of another cell?


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







Is there a way I can use conditional formatting or something to change the color of the cell once I enter a value or text into that cell? For example if I currently have the A2 cell color as a gray color, and I enter information into A2 can I have it set to automatically change to no fill or any other color without having to click out of the cell, back into it, and then clicking the button?? Does that make sense??? Can someone help me?


I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

 
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
    For Each cell In myRange
    If cell.Value < 2 Then cell.Font.ColorIndex = 5
    If cell.Value < 1 Then cell.Font.ColorIndex = 3
    Next





How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified
color.

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.



I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?



Hi,

I am looking to change how an active cell is viewed. For instance when you are looking at your spreadsheet you can see the active cell has a thin black border around it. I would like to change the border to a thicker one and change the color to blue or red. I am trying to make it easier to the eye so when you are working on a large spreadsheet it is immediatly apparent where you are to avoid squinting.

If anyone can help me please let me know.

Is it possible to change the color of text using a formula, such as an IF
statement? What I have in mind is something like this (stated simply): If
cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4
red.




Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.


Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


Hello, please help me out with this one.

So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!


I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example: If the call came in between 22:00 and 23:59 color is light green.
If the call came in between 23:59 and 08:00 the color is yellow.

Example spreadsheet is attached.

Thanks in advance.


Can you put a diagonal line in a cell and then color one part of it one color and the ther part of it another color?


I am a very advanced Excel user so this is a new one for me.

When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.

In addition:

Can not fill series
Can not change cell format (specifically to show Currency or Accounting w/$ symbol)

And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.

Help!


I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).

For instance,

A B
1 $32.17 8/17/08
2 $43.20 9/12/09

If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.


Thanks


Hello,

I need to know how to auto-fill text based on text in another cell. For
example:

Every time I enter "CHS" in Column B, I want Charleston to auto-fill in
Column C.
And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column
C.

How can I set up a list like this? Any ideas?

Thanks!

Jason



I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

[CODE]ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
'After filtering I want to change the value in column Y for only the filtered entries CODE]


Hi everyone,

I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.

How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.

Thanks in advance,

Mike




Hi guys,

I was wondering if it is possible to change the color of a command button after it's been clicked?

This is to allow the user to identify which buttons have already been clicked.

Thanks!


Hello all,

Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:

Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))

This does the autofill, but doesn't stop at the last cell with data in Column B.

In the past I have use this code to acheive similar results:

Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)

The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.

All help is appreciated. Thanks!


Hi Guys

I have the following in G2:

=VLOOKUP(A2,Sheet1!A:D,4,FALSE)

The formula returns the correct result, which in this case is a number - 2

When I fill down my range, the formula copies correctly, but every result is the same. 2.

However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.

Any ideas whats going wrong?


Let's say, if two fields in one column has been filled with numerical
increament, i.e. A1 is 2 and A2 is 4. Now I want to fill the whole column
with this increament pattern till row A20. One thing we will all do is to
left click on the fill handle and drag it down to row A20. But if the task is
to fill to row 500, the mouse draging sometime won't be easy to locate the
row. I am wondering anyone can help me to find the keyboard shortcut
equivalent to that drag fill handle action.

Thanks



I have an extensive spreadsheet with several columns and all showing borders
where each cell/column begins and ends. However there are a few cells where
the break with the next cell does not show the line.

I have highlighed the cell in question, gone to format/cell/borders and
everything looks fine. Black color + format border shows square with all
sides of square showing.

Any thoughts on how to fix these few random cells.

Thanks