CODEWITHSUNDEEP
excelvbaconditional-formatting
Mat257
Mat257

Reputation: 33

Multiple overlapping conditional formatting

I wrote down below code:

    Sub formatCondMesi()
Worksheets(1).Unprotect Password:="ponzio"
Dim i As Integer
'For i = 2 To 13
Worksheets(1).Select
Worksheets(1).Cells.FormatConditions.Delete
'1)
With Worksheets(1).Application.Union(Range("C7:O149"), Range("C155:O297"), Range("C303:O445"), Range("C451:O593"))
.FormatConditions.Add Type:=xlExpression, Formula1:="=$H7<0"
.FormatConditions(1).Font.ColorIndex = 3
End With
'2)
With Worksheets(1).Application.Union(Range("J150"), Range("L150"), Range("N150:O150"))
.FormatConditions.Add Type:=xlExpression, Formula1:="=$E150="""""
.FormatConditions(1).Font.ColorIndex = 2
End With

'3)
i = 1
If i = 1 Then
    A = Worksheets(1).Range("F4").Value
    B = Worksheets(1).Range("F5").Value
End If
With Worksheets(1).Application.Union(Range("E7:E149"), Range("E155:E297"), Range("E303:E445"), Range("E451:E593"))
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($E7<" & A & ";$E7>" & B & ")"  '43101 43131
'.FormatConditions(1).SetFirstPriority = True
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.Strikethrough = True
End With

'4)
With Worksheets(1).Application.Union(Range("O7:O149"), Range("O447:O593"), Range("O299:O445"), Range("O151:O297"))
.FormatConditions.Add Type:=xlExpression, Formula1:="=$O7=0"
.FormatConditions(1).Font.ColorIndex = 3
End With

'5)
With Worksheets(1).Application.Union(Range("L7:L149"), Range("L151:L297"), Range("L299:L445"), Range("L447:L593"))
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISNA($L7)"
.FormatConditions(1).Font.ColorIndex = 2
End With

End Sub

I have two issues:

  1. when I reach CF n. 3 ('3) it wrongly apply formats, the strike-through line is applied in another rule and bold style too..and i can't understand with which criteria it is selecting other CF

  2. when I reach CF n.4 it returns:

    1004 error, object not defined

Upvotes: 1

Views: 293

Answers (1)

FunThomas
FunThomas

Reputation: 29171

For issue 1), probably you have to add

.FormatConditions(1).StopIfTrue = True

else, all conditional formatting rules will be checked and applied

For issue 2): Do you work by any chance with Excel 2003? There, the max. number of rules was limited to 3, so maybe that's your issue. Else I have no idea, I made a quick test and could add more than 3 rules without problem.

Upvotes: 4

Related Questions