Cell Font Color based on another Cell value [VBA]

[Martin_H]

Hi team,

let there be a range L4:L126.

In this range, the cells can only acquire the values W4, or W5, or W6, or W7, or JR, or the cell is just empty.

If the cell in the range - let's just say L6 - has a value W4, then color the font of the cell C6 to RGB(0, 130, 59) else,
If the cell in the range - let's just say L8 - has a value W5, then color the font of the cell C8 to RGB(0, 0, 0) else,
If the cell in the range - let's just say L25 - has a value W6, then color the font of the cell C25 to RGB(255, 0, 0) else,
If the cell in the range - let's just say L111 - has a value W7, then color the font of the cell C111 to RGB(128, 10, 246) else,
If the cell in the range - let's just say L87 - has a value JR, then color the font of the cell C87 to RGB(128, 0, 0) else,
If the cell in the range - let's just say L120 - is BLANK (empty), then color the font of the cell C120 to RGB(191, 191, 191) end.


Thank you for your help.

Much appreciated.

 

[dave3009]

Hi

You've mentioned VBA, is there a reason you can't use normal conditional formatting?

You would select range L4:L126, then in conditional formatting new rule:
1. Format Cells that Contain
2. Specific Text, equal to, W4
3. Set font colour accordingly
4. Repeat for each of your conditions.

HTH, Dave

 

[maabadi]

Try this:

Sub ChangeFontColor()
Dim i As Long, Lr As Long
Lr = Range("L" & Rows.Count).End(xlUp).Row
' Convert RGB to Long = Red + Green * 256 + Blue * 65536
For i = 4 To Lr
Select Case LCase(Range("L" & i).Value)
    Case "w4"
        Range("C" & i).Font.Color = 3899904
    Case "w5"
        Range("C" & i).Font.Color = 0
    Case "w6"
        Range("C" & i).Font.Color = 255
    Case "w7"
        Range("C" & i).Font.Color = 16124544
    Case "jr"
        Range("C" & i).Font.Color = 128
    Case ""
        Range("C" & i).Font.Color = 12566463
End Select
Next i
End Sub

And if you want worksheet Change event to whenever you change text at column L Macro runs. then
Right-Click on Sheet name , Select view code and Paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Lr As Long
Lr = Range("L" & Rows.Count).End(xlUp).Row
' Convert RGB to Long = Red + Green * 256 + Blue * 65536
If Intersect(Target, Range("L4:L" & Lr)) Is Nothing Then Exit Sub
i = Taget.Row
Select Case LCase(Range("L" & i).Value)
    Case "w4"
        Range("C" & i).Font.Color = 3899904
    Case "w5"
        Range("C" & i).Font.Color = 0
    Case "w6"
        Range("C" & i).Font.Color = 255
    Case "w7"
        Range("C" & i).Font.Color = 16124544
    Case "jr"
        Range("C" & i).Font.Color = 128
    Case ""
        Range("C" & i).Font.Color = 12566463
End Select
End Sub

 

[Martin_H]

Try this:

Sub ChangeFontColor()
Dim i As Long, Lr As Long
Lr = Range("L" & Rows.Count).End(xlUp).Row
' Convert RGB to Long = Red + Green * 256 + Blue * 65536
For i = 4 To Lr
Select Case LCase(Range("L" & i).Value)
    Case "w4"
        Range("C" & i).Font.Color = 3899904
    Case "w5"
        Range("C" & i).Font.Color = 0
    Case "w6"
        Range("C" & i).Font.Color = 255
    Case "w7"
        Range("C" & i).Font.Color = 16124544
    Case "jr"
        Range("C" & i).Font.Color = 128
    Case ""
        Range("C" & i).Font.Color = 12566463
End Select
Next i
End Sub

And if you want worksheet Change event to whenever you change text at column L Macro runs. then
Right-Click on Sheet name , Select view code and Paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Lr As Long
Lr = Range("L" & Rows.Count).End(xlUp).Row
' Convert RGB to Long = Red + Green * 256 + Blue * 65536
If Intersect(Target, Range("L4:L" & Lr)) Is Nothing Then Exit Sub
i = Taget.Row
Select Case LCase(Range("L" & i).Value)
    Case "w4"
        Range("C" & i).Font.Color = 3899904
    Case "w5"
        Range("C" & i).Font.Color = 0
    Case "w6"
        Range("C" & i).Font.Color = 255
    Case "w7"
        Range("C" & i).Font.Color = 16124544
    Case "jr"
        Range("C" & i).Font.Color = 128
    Case ""
        Range("C" & i).Font.Color = 12566463
End Select
End Sub

Not sure why, but it only change the color if the cell is empty

 

[maabadi]

1. W4 is Cell Address or Cell Value ??
2. Are you don't have more space at the end of your data at column L?

if you have space try changing Select Case Line code to this

Select Case LCase(Trim(Range("L" & i).Value))

3. you run Normal macro or worksheet change event?

 

[Martin_H]

1. W4 is Cell Address or Cell Value ??
2. Are you don't have more space at the end of your data at column L?

if you have space try changing Select Case Line code to this

Select Case LCase(Trim(Range("L" & i).Value))

3. you run Normal macro or worksheet change event?

1. W4 is Cell Value.
2. No.
3. I run Worksheet change event.

 

[maabadi]

Worksheet change event only on change color of cell that value changed. For changing all cells color at first time, you should run normal macro.

 

[Martin_H]

Worksheet change event only on change color of cell that value changed. For changing all cells color at first time, you should run normal macro.

Yeah, I understand.

Each time I change the value in column L, the font color in column C does not change.

This only works if the cell value in column L is empty. Then the color actually changes to .Color = 12566463

However, it does not respond to W4, W5, W6, W7 values ?

 

[maabadi]

Sorry my fault. Change Lr to :

Lr = Range("C" & Rows.Count).End(xlUp).Row

Then Code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Lr As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
' Convert RGB to Long = Red + Green * 256 + Blue * 65536
If Intersect(Target, Range("L4:L" & Lr)) Is Nothing Then Exit Sub
i = Target.Row
Select Case LCase(Range("L" & i).Value)
    Case "w4"
        Range("C" & i).Font.Color = 3899904
    Case "w5"
        Range("C" & i).Font.Color = 0
    Case "w6"
        Range("C" & i).Font.Color = 255
    Case "w7"
        Range("C" & i).Font.Color = 16124544
    Case "jr"
        Range("C" & i).Font.Color = 128
    Case ""
        Range("C" & i).Font.Color = 12566463
End Select
End Sub

Or if you have fixed range try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
' Convert RGB to Long = Red + Green * 256 + Blue * 65536
If Intersect(Target, Range("L4:L126")) Is Nothing Then Exit Sub
i = Target.Row
Select Case LCase(Range("L" & i).Value)
    Case "w4"
        Range("C" & i).Font.Color = 3899904
    Case "w5"
        Range("C" & i).Font.Color = 0
    Case "w6"
        Range("C" & i).Font.Color = 255
    Case "w7"
        Range("C" & i).Font.Color = 16124544
    Case "jr"
        Range("C" & i).Font.Color = 128
    Case ""
        Range("C" & i).Font.Color = 12566463
End Select
End Sub