Change the font color based on the value of the cell

[Robin]

How do I create a macro or program to change the font color of a particular
cell based on the value of the cell, e.g. 1 3 5 7, change the font color to
red, if 0, 00 change font color to green and bold, thanks in advance

 

[JLGWhiz]

If Range("A1").Value = SomeValue Then
Range("A1").Font.ColorIndex = 3 'ColorIndex 3 is Red
End If

 

[JLGWhiz]

For more than one value:

If Range("A1").Value = SomeValue Then
Range("A1").Font.ColorIndex = 3
ElseIf Range("A1").Value = AnotherValue Then
Range("A1").Font.ColorIndex = 12
End If

Or you could use select case if you have several values to check.

 

[Robin]

what if i wanted to include the values 1 3 5 7 how do i code the SomeValue

 

[Rick Rothstein]

Doing a Roulette worksheet, huh? <g>

I think Conditional Formatting would be the best to use. Select your
"particular cell" (I'll assume you selected A1 for this example...change the
A1 in the formulas below to whatever the address is for the actual
"particular cell" you selected) and then click Format/Conditional Formatting
from the menu bar. On the dialog box that comes up, select "Formula Is" from
the first drop down and then put this formula in the empty field to its
right...

=AND(MOD(A1,2)=1,A1<>"")

Then click the Format button, select the Patterns tab and pick the red color
you want. If you pick a dark red color, then you might want to click the
Font tab and make the Font color white for readability. When done, click OK
to go back to the first dialog box. Next, click the Add button and select
"Formula Is" for Condition 2 (the condition you just Add'ed) and put this
formula in the empty field to its right...

=AND(OR(A1="0",A1="00"),A1<>"")

 

[CurlyDave]

what if i wanted to include the values 1 3 5 7 how do i code the SomeValue

The somevalue is there for you to hard code, replace somevalue with a
value, replace another value with another value

 

[Robin]

yes roulette worksheet =) thanks

Doing a Roulette worksheet, huh? <g>

I think Conditional Formatting would be the best to use. Select your
"particular cell" (I'll assume you selected A1 for this example...change the
A1 in the formulas below to whatever the address is for the actual
"particular cell" you selected) and then click Format/Conditional Formatting
from the menu bar. On the dialog box that comes up, select "Formula Is" from
the first drop down and then put this formula in the empty field to its
right...

=AND(MOD(A1,2)=1,A1<>"")

Then click the Format button, select the Patterns tab and pick the red color
you want. If you pick a dark red color, then you might want to click the
Font tab and make the Font color white for readability. When done, click OK
to go back to the first dialog box. Next, click the Add button and select
"Formula Is" for Condition 2 (the condition you just Add'ed) and put this
formula in the empty field to its right...

=AND(OR(A1="0",A1="00"),A1<>"")

 

[muddan madhu]

may be this one

assumed your value is in Cell A1

Private Sub worksheet_change(ByVal target As Range)
target = Range("A1")
For a = 1 To 7 Step 2
If target = a Then
target.Font.ColorIndex = 3
Exit Sub
Else
target.Font.ColorIndex = 0
End If
Next
End Sub

 

[Rick Rothstein]

I gave you some bad advice... I gave you how to change the fill color, but I
see you want to change the font color. Instead of choosing Patterns, select
the Font tab and just choose the color you want your font to be. All other
instructions are as I posted them.

 

[Rick Rothstein]

If you really want VB code instead of the Conditional Formatting I
recommended earlier, then use this worksheet event code instead (where you
would change the A1 to the cell address of the "particular cell" you want to
have this functionality)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) <> "A1" Then Exit Sub
If Target.Value Mod 2 = 1 Then
Target.Font.ColorIndex = 3
ElseIf Target.Value = "0" Or Target = "00" Then
Target.Font.ColorIndex = 4
Else
Target.Font.ColorIndex = 0
End If
End Sub