VBA Change color of cell next to another cell in column, given certain values

[MrSmith484]

Dear Excel forum - excel newbie here.

I want to get conditional formating, and hence need to construct a code in VBA, that for a given column (or clomuns) makes a cell next to it a specific colour (e.g. red) given that the value of the column (or columns) is e.g 5 (or at least 5)

The code i have tried so for looks as following, but doensīt seem to work at all - itīs constructed from copy/pasting other solutions, for similar problems:


Private Sub colourChange(ByVal Target As Integer)


On Error Resume Next

If Target.Column = 7 Then

If Taget.Value = 5 Then

Application.EnableEvents = False

Target.Offset(0, 4).Interior.ColorIndex = 3

End If

End If



exitHandler:

Application.EnableEvents = True

Exit Sub


End Sub


Here i want a cell in colomn K to turn red, if a cell in column G in the same row has the value 5.


I hope you can help me solve my problem,

Thank you in advance

[Bernie Deitrick]

I have assumed that you are entering values into column G (not using formulas):

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.

Please Login or Register  to view this content.

If you want to run a macro instead of using event code, then you could use this (which will work on constants or formulas in G):

Please Login or Register  to view this content.

[MrSmith484]

It works!!

Thank you so much - you're an everyday hero! :D