Any one know why this macro is not working?

[tonywatsonhelp]

Hi Everyone, this macro works great but as soon as i added

Sub ConditionalFormatting()
Num = ActiveSheet.Range("AD108").Value + 108

Dim MyRange As Range
Dim MyRange2 As Range
Dim MyRange3 As Range
Dim MyRange4 As Range

Set MyRange = Range("AE111:AP" & Num)
Set MyRange2 = Range("AE111:AI" & Num)
Set MyRange3 = Range("AJ111:AJ" & Num)
Set MyRange4 = Range("AK111:AP" & Num)

MyRange.FormatConditions.Delete

MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($AA111=""Title"",TRUE)"
MyRange.FormatConditions(1).Interior.Color = RGB(198, 224, 180)
MyRange.FormatConditions(1).Font.Color = RGB(68, 104, 86)
MyRange.FormatConditions(1).Font.Bold = True
'MyRange.FormatConditions(1).Borders.Color = RGB(68, 104, 86)
MyRange.FormatConditions(1).StopIfTrue = False

MyRange2.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($AA111=""Rate"",TRUE)"
MyRange2.FormatConditions(2).Interior.Color = RGB(230, 240, 225)
MyRange2.FormatConditions(2).Font.Color = RGB(68, 104, 86)
MyRange2.FormatConditions(2).Borders.Color = RGB(68, 104, 86)
MyRange2.FormatConditions(2).StopIfTrue = False

'it worked great until i added these?

MyRange3.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($AA111=""Rate"",TRUE)"
MyRange3.FormatConditions(3).Interior.Color = RGB(255, 255, 255)
MyRange3.FormatConditions(3).Font.Color = RGB(68, 104, 86)
MyRange3.FormatConditions(3).Borders.Color = RGB(68, 104, 86)
MyRange3.FormatConditions(3).StopIfTrue = False

MyRange4.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($AA111=""Rate"",TRUE)"
MyRange4.FormatConditions(1).Interior.Color = RGB(230, 240, 225)
MyRange4.FormatConditions(1).Font.Color = RGB(68, 104, 86)
MyRange4.FormatConditions(1).Borders.Color = RGB(68, 104, 86)
MyRange4.FormatConditions(1).StopIfTrue = False



End Sub

 

[mikerickson]

Your Format condition doesn't add borders, so there is no .FormatConditions(1).Borders to color.

 

[Fluff]

Care to give us a clue?

 

[jasonb75]

Not working as in
Runtime error?
Does nothing?
Does something but not what is expected?
Something else?

Noting that you're setting condition 3 on MyRange3, it there are less than 3 conditions on that range then that might cause an error (not sure without setting up a test).

As you're adding new conditions but not deleting existing ones, you might not necessarily be setting the rules to the correct condition. From memory, new rules will have the highest index number by default.

If that is the problem then you could use Myrange3.FormatConditions.Count to force the code to work with the newly added rule.

 

[tonywatsonhelp]

Hi Jason75,
Thasts it thanks you
thank you to everyone for trying to help
now fixed
Tony

 

[jasonb75]

Your Format condition doesn't add borders, so there is no .FormatConditions(1).Borders to color.

Do the borders need to be specifically added in CF, or would it colour any existing standard format borders?
I don't think I've ever had cause to use borders with CF, but thought that this would be how it would work.

@tonywatsonhelp

If you've been running the code multiple times then it might be a good idea to do a clean up on your CF rules for those ranges. As you're adding new rules without deleting old ones, you could be building up a quite a collection of duplicate rules.

 

[Fluff]

It should create a border around the cell with the specified colour, regardless of whether there is an existing border or not.

 

[mikerickson]

Do the borders need to be specifically added in CF, or would it colour any existing standard format borders?
I don't think I've ever had cause to use borders with CF, but thought that this would be how it would work.

@tonywatsonhelp

If you've been running the code multiple times then it might be a good idea to do a clean up on your CF rules for those ranges. As you're adding new rules without deleting old ones, you could be building up a quite a collection of duplicate rules.

AFAIK, Conditional Formatting either adds the borders specified or it doesn't address borders. "Color the existing borders, what ever they may be" is not an option.