How to change color of cell B1 based on value in cell A1

[dougmcc1]

How do I get the background color of cell B1 to change when a value i
entered into cell A1? I'd like for the color of the background to var
depending on the value. Any ideas

 

[sungen99]

This will help.





With A1 the active cell (and red fill color), select Format>Conditiona
Formatting...

Set Condition 1 to 'Cell Value Is' Greater Than B1 click the Patter
tab and choose your fill color (green?) and click OK

Does this work for you?
__________________
Bruce
The older I get, the better I used to be.
Minneapolis, MN US

 

[Bernard Liengme]

Use Help to learn about Condition Formatting (look in the Format menu)
Come back if you have questions after that
best wishes

 

[dougmcc1]

Conditional formatting wont work because this is what I need:
if a1=1, b2 bg color=red
else if a1=2, b2 bg color=orange
else if a1=3, b2 bg color=yellow
else if a1=4, b2 bg color = green
etc.

A macro wont work for what I want to do because I'd like for the b
color to change right away instead of running a macro multiple times

 

[Bucky]

A macro wont work for what I want to do because I'd like for the bg
color to change right away instead of running a macro multiple times.

You can have the macro be triggered on every selection change, which is
basically any change in the worksheet. I did something similar with a
macro at the sheet level. You can customize depending on the range of
cells that you need. Here's an example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo CleanUp:
Application.EnableEvents = False

Dim rCell As Range
For Each rCell In UsedRange ' colorize this range of cells
With rCell
Select Case .Value
Case "G": .Interior.ColorIndex = 4 ' green
Case "B": .Interior.ColorIndex = 8 ' blue
Case "Y": .Interior.ColorIndex = 6 ' yellow
Case "O": .Interior.ColorIndex = 45 ' orange
Case "R": .Interior.ColorIndex = 3 ' red
End Select
End With
Next rCell

CleanUp:
Application.EnableEvents = True

End Sub

 

[dougmcc1]

This would be very interesting if it does what i think it's suppose to
do, but so far I cant get it to work. I copied your code, created a new
macro with it called "active_macro", saved, and made some changes to the
sheet but nothing happened.

How do I trigger it?

 

[Bucky]

How do I trigger it?

The way that I implemented it was at the worksheet level. Right click
on the worksheet (Sheet1) tab > View Code. Paste the code in there. For
debugging purposes, it would be helpful to add a msgbox "hi" in the
first line to see if it gets triggered.

 

[David McRitchie]

You would need an Event macro, assuming that you type
the value into the cell you would use a Change Event macro.
http://www.mvps.org/dmcritchie/excel/event.htm#case

The limitation on Conditional Formatting is that you can only
have three color choice each with a formula. For more than
three another option would be to use the Extended Conditional
Formatting addin at xldynamic.com
http://www.mvps.org/dmcritchie/excel/condfmt.htm
http://www.xldynamic.com/source/xld.CFPlus.Download.html