PDA

View Full Version : [SOLVED] Changing Font Color



pegbol
02-19-2005, 01:36 PM
.
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.: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,
.

Jacob Hilderbrand
02-19-2005, 04:42 PM
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
02-19-2005, 05:43 PM
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.

Jacob Hilderbrand
02-19-2005, 05:49 PM
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
02-19-2005, 06:03 PM
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
02-19-2005, 06:23 PM
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
02-19-2005, 06:37 PM
: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.

Jacob Hilderbrand
02-19-2005, 06:52 PM
You're Welcome :beerchug:

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

Application.ScreenUpdating = False

pegbol
02-19-2005, 07:17 PM
:bow: :bow: :thumb :clap: :hi: :bow: :bow:

Anne Troy
02-19-2005, 08:20 PM
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.