I am trying to set 10 separate conditional formatting rules against multiple named ranges located in the same workbook but on different worksheets and can't seem to get it to work. The VBA code that I have is below:
Sub CondFormat()
'Conditional format named ranges
Dim nr As Range
Set nr = Range("IS2C", "IS3C", "IS4C")
nr.FormatConditions.Delete
'Add Rule 1
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(nr))=0"
nr.FormatConditions(1).Style = "BLANK"
nr.FormatConditions(1).StopIfTrue = True
'Add Rule 2
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(nr))=0"
nr.FormatConditions(2).Style = "BLANK"
nr.FormatConditions(2).StopIfTrue = True
'Add Rule 3
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""W"""
nr.FormatConditions(3).Style = "WITHDRAWN"
nr.FormatConditions(3).StopIfTrue = True
'Add Rule 4
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""T"""
nr.FormatConditions(4).Style = "TERMINATED"
nr.FormatConditions(4).StopIfTrue = True
'Add Rule 5
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""P2"""
nr.FormatConditions(5).Style = "PASS2"
nr.FormatConditions(5).StopIfTrue = True
'Add Rule 6
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""NR"""
nr.FormatConditions(6).Style = "PASS1"
nr.FormatConditions(6).StopIfTrue = True
'Add Rule 7
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""ü"""
nr.FormatConditions(7).Style = "PASS1"
nr.FormatConditions(7).StopIfTrue = True
'Add Rule 8
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SUM($E11+I$8)>$A$8"
nr.FormatConditions(8).Style = "FUTURE"
'Add Rule 9
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SUM($E11+I$8)<$A$8"
nr.FormatConditions(9).Style = "LATE"
'Add Rule 10
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(nr))>0"
nr.FormatConditions(10).Style = "FUTURE"
End Sub
Any help or guidance on where I am going wrong would be gratefully received!
Sub CondFormat()
'Conditional format named ranges
Dim nr As Range
Set nr = Range("IS2C", "IS3C", "IS4C")
nr.FormatConditions.Delete
'Add Rule 1
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(nr))=0"
nr.FormatConditions(1).Style = "BLANK"
nr.FormatConditions(1).StopIfTrue = True
'Add Rule 2
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(nr))=0"
nr.FormatConditions(2).Style = "BLANK"
nr.FormatConditions(2).StopIfTrue = True
'Add Rule 3
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""W"""
nr.FormatConditions(3).Style = "WITHDRAWN"
nr.FormatConditions(3).StopIfTrue = True
'Add Rule 4
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""T"""
nr.FormatConditions(4).Style = "TERMINATED"
nr.FormatConditions(4).StopIfTrue = True
'Add Rule 5
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""P2"""
nr.FormatConditions(5).Style = "PASS2"
nr.FormatConditions(5).StopIfTrue = True
'Add Rule 6
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""NR"""
nr.FormatConditions(6).Style = "PASS1"
nr.FormatConditions(6).StopIfTrue = True
'Add Rule 7
nr.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""ü"""
nr.FormatConditions(7).Style = "PASS1"
nr.FormatConditions(7).StopIfTrue = True
'Add Rule 8
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SUM($E11+I$8)>$A$8"
nr.FormatConditions(8).Style = "FUTURE"
'Add Rule 9
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SUM($E11+I$8)<$A$8"
nr.FormatConditions(9).Style = "LATE"
'Add Rule 10
nr.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(nr))>0"
nr.FormatConditions(10).Style = "FUTURE"
End Sub
Any help or guidance on where I am going wrong would be gratefully received!