Conditional Formatting named ranges in VBA

[DYB]

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!

 

[rlv01]

The purpose of conditional formatting is to change the format of the cells in the range depending on the cell contents or formula based on cell contents. You are creating formatconditions, but you are not defining any formats in your code for those newly created formatconditions. I suggest you use the Macro Recorder to record your actions as you manually create the a format condition for just one range . Then post that code to see wat might be done to apply it to your multiple ranges. (make sure the format condition you create manually actually works to your satisfaction before posting)

Also, please try to use code tags as per below when posting your code. It makes it easier to read.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com