Changing Font Color

[pegbol]

.
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)

=AND($J9>$K9)

this formula works OK.

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

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.

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

Please, help me.

Thanks so much in advance.

regards,
.

[Jacob Hilderbrand]

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

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

[pegbol]

Jake,

Thanks so much for your kind solution.

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

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

regards,
Pedro.

[Jacob Hilderbrand]

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.

[pegbol]

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".

[Jacob Hilderbrand]

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

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

[pegbol]

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

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

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.

[Jacob Hilderbrand]

You're Welcome

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

Application.ScreenUpdating = False

[pegbol]

[Anne Troy]

Also, whenever you use:

Application.ScreenUpdating = False 

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

Application.ScreenUpdating = True

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