Hi All,
I don't know if this is possible. I have attempted to adapt my code for the same thing, but referencing a sheet with a set name. But no joy, it loops throught the workbooks but doen't apply the formatting. I think this is due to the referencing in the formula.
Any Thoughts?
Sub conditionaFormat()
Dim wks As Worksheet
Dim rng As Range
Dim i As Integer
Dim prevname As String
prevname = Sheets(ActiveSheet.Index - 1).Name
For i = 3 To Sheets.Count
Set wks = Sheets(i)
wks.Select
On Error GoTo NextSheet:
Set rng = Range("A1:I230")
rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=A1<'prevname'!A1"
rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With rng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
rng.FormatConditions(1).StopIfTrue = False
rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=A1>'prevname'!A1"
rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With rng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
rng.FormatConditions(1).StopIfTrue = False
NextSheet:
Resume NextSheet2
MsgBox "No Difference"
NextSheet2:
Next i
End Sub
Thank you!!
Bookmarks