Excel Automating Conditional Formatting

[hafizt]

Hello Forum Friends,


I need your help in conditional formatting in Excel.

I have two spreadsheets (sheet 1 and 2). My users update the sheet 1 and my conditional formatting is working fine in it. However, for sheet 2 which I linked to sheet 1 using vlookup formula, although values are changed based on values in sheet 1 but conditional formatting doesn’t work automatically and I have to click F2 and hit enter for conditional formatting to work.


Is there a code that I could use to automatically update the conditional formatting?


Here is my code:

Option Compare Text 'A=a, B=b, ... Z=z

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range

Dim Rng1 As Range


On Error Resume Next

Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

On Error GoTo 0

If Rng1 Is Nothing Then

Set Rng1 = Range(Target.Address)

Else

Set Rng1 = Union(Range(Target.Address), Rng1)

End If

For Each Cell In Rng1

Select Case Cell.Value

Case vbNullString

Cell.Interior.ColorIndex = xlNone

Cell.Font.Bold = False

Case "V", "V½"

Cell.Interior.ColorIndex = 28

Cell.Font.Bold = True

Case "VP", "PC"

Cell.Interior.ColorIndex = 3

Cell.Font.Bold = True

Case Else

Cell.Interior.ColorIndex = xlNone

Cell.Font.Bold = False

End Select

Next

End Sub

 

[Luke M]

The problem is that a linked cell doesn't actually "change" it's contents, so the event macro is not getting triggered. I'd recommend using the Worksheet_Calculate event. Note that you will need to redefine how you set Rng1, as there is no longer a "Target" in the calculate event.