Changing Font Color

.
Hello Experts,
I have values in columns J and K. These values increase or decrease when I use an advanced filter. I need change the font color of the values in column K based on the values of column J.

Example:
If J9>K9, then the font color of K9 is red.
If J9<K9, then the font color of K9 is normal (black).

I put in the conditional formatting menu: (column K)

Code:

---

=AND($J9>$K9)

---

this formula works OK.

But, I do need make it with VBA code. So, I wrote:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
If Target.Offset(0, -1).Value > Target.Value Then
Target.Font.ColorIndex = 3
End If
End If
End Sub

---

This code has a problem, cause only change the font color one time. If I use the advanced filter nothing happens in column K when the values change.:banghead:

In short, what I need is a VBA code that works exactly as the same of conditional formatting.

Please, help me. : pray2:

Thanks so much in advance.

regards,
.

Try this. Just run the AdvFilter macro when you want to filter the range.

Code:

---

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel            As Range
For Each Cel In Target
        If Cel.Column = 11 Then
            If Cel.Offset(0, -1).Value > Cel.Value Then
                Cel.Font.ColorIndex = 3
            Else
                Cel.Font.ColorIndex = 0
            End If
        End If
    Next
End Sub

Sub AdvFilter()
Dim RngFrom        As Range
Dim RngTo          As Range
Dim Cel            As Range
Set RngFrom = Range("I2:I14")
    Set RngTo = Range("K2:I14")
RngFrom.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=RngTo(1, 1), Unique:=False
For Each Cel In RngTo
        If Cel.Offset(0, -1).Value > Cel.Value Then
            Cel.Font.ColorIndex = 3
        Else
            Cel.Font.ColorIndex = 0
        End If
    Next
End Sub

---

Jake,

Thanks so much for your kind solution.

A favor. Would you help me where I have to input the code in the VBE?.:bow:
I tried and tried, but I do not realize.:banghead:
Enclosed is my file.

I apologize if I did not make my request much so clear.

regards,
Pedro.

Ok, I tried your attachment and when I press the Click button everything seems to color just fine. Let me know exactly what you are doing that is not working.

Jake,

Yes, when you click the button the code color red all the values.

But my problem starts when I use the filter.
Ex: Write in cell E5---> BG, and in F5---> ACT

You will see that K13 and K15 does not change color to black.

Pedro.


PS.The color of the values should be exactly the same as Sheet "Example".

Replace the code you have in your Worksheet Code Module with this.

Code:

---

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'FILTER
Dim LastRow        As Long
Dim i              As Long
If Target.Count > 1 Then Exit Sub
    If Target.Address = "$E$5" Or _
      Target.Address = "$F$5" Or _
      Target.Address = "$G$5" Then
        Range("base").AdvancedFilter _
                Action:=xlFilterInPlace, _
                CriteriaRange:=Range("criterio"), Unique:=False
        LastRow = Range("K65536").End(xlUp).Row
        For i = 9 To LastRow
            If Range("J" & i).Value > Range("K" & i).Value Then
                Range("K" & i).Font.ColorIndex = 3
            Else
                Range("K" & i).Font.ColorIndex = 0
            End If
        Next i
    End If
'FONT COLOR RED COLUMN K
    If Target.Column = 11 Then
        If Target.Offset(0, -1).Value > Target.Value Then
            Target.Font.ColorIndex = 3
        Else: Target.Font.ColorIndex = xlAutomatic
        End If
    End If
End Sub

---

:bow: :clap:

Yes!!!, your code shows the right colors when I filter the registers.

Thanks soooooooo much!!!!! Jake. You are the man!!!!:bow:

kindest regards,
Pedro.
La Paz, BOLIVIA.


PS. Just for information. The code runs a little slowly. Is there a way to make it faster?. If not possible, it is OK. I appreciate your kind help.

You're Welcome :beerchug:

To make is faster add this to the beginning of the macro.

Code:

---

Application.ScreenUpdating = False

---

:bow: :bow: :thumb :clap: :hi: :bow: :bow:

Also, whenever you use:

Code:

---

Application.ScreenUpdating = False

---

at the beginning of your code, you likely want to add the following line before your end sub:

Code:

---

Application.ScreenUpdating = True

---

:)

I am changing the title of this thread to something more meaningful for people who may find it in a search.