[VBA] Conditional Formatting

[Eddie_SP]

Hi Community !

I need help.

I do not want to use "Conditional Formatting" in Excel. I need it by VBA.

If I put any value in a cell of column "D", columns A, B and C must be
changed to red colour.
I will need it in many cases, but as I've never worked with Cond. Form. in
VBA, it will help me in many ways in the future.

I thank you in advance ! =)

 

[JLGWhiz]

copy and paste this code into the worksheet code module. To open the module
window, right click the sheet tab and click View Code in the pop up menu.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D")) Is Nothing Then
Range("A" & Target.Row & ":C" & Target.Row) _
.Interior.ColorIndex = 3
End If
End Sub

 

[Eddie_SP]

Hi JLGWhiz !

First of all, thank you very much !!! =)

I changed it a little:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D")) Is Nothing Then
Range("A" & Target.Row & ":C" & Target.Row) _
.Font.ColorIndex = 3
Else
Range("A" & Target.Row & ":C" & Target.Row) _
.Font.ColorIndex = 1
End If
End Sub

But when I delete the value from Column D, it does not change the colour to
black, only if I chande any valeu from the column on the left (A,B,C).
Do you know what how to correct that ?

 

[Rick Rothstein]

The following event code will change the same row in Columns A, B and C to
red if you enter a value into Column D and will remove that color if you
delete the value in Column D. It will also allow you to make changes to
multiple cells at one time as well.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If R.Column = 4 Then
With R.Offset(, -3).Resize(1, 3).Interior
If Len(R.Value) > 0 Then
.ColorIndex = 3
Else
.ColorIndex = 0
End If
End With
End If
Next
End Sub

To install it, right click the tab at the bottom of the worksheet you want
it to apply to, select View Code from the pop up menu that appears and then
copy/paste the above code into the code window that appears.

 

[Rick Rothstein]

I see from you response to JLGWhiz that you wanted the font color to change,
not the cell interior. Here is the code to use for that...

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

 

[Eddie_SP]

Wow, Rick !

Great !!!

I changed it too:

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

Thank you !!!

I'm suffering trying to rate the posts, when I click on "YES", nothing
happens !!! =(
It says "thank you", but as I see, it doesn't rate it...


Well, THANK YOU RICK AND JLGWhiz !!!!

 

[Rick Rothstein]

You are quite welcome, of course.

Don't worry about the "Yes" option for me... it is an option for those who use the web interface when answering questions, which I do not do (I use my newsreader instead).

If you are interested, and if you are willing to use a ColorIndex of 0 (Excel translates 0 into xlColorIndexAutomatic, the Automatic color) instead of 1 (black), which I think is a better way to remove the red color, then we can shorten the code considerably...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If R.Column = 4 Then R.Offset(, -3).Resize(1, 3).Font.ColorIndex = -3 * (Len(R.Value) > 0)
Next
End Sub