Conditional Format Macro Issues

[metzgek]

Hi all - I really hope someone out there can help, because I just can't figure this problem out, and it's driving me crazy! Here's the deal...

I have a spreadsheet that has conditional formatting, but users need to be able to paste into it. Because user pasting may mess with the conditional formatting, I want to build a macro that will undo all the formatting and reset all of the conditional formats.

I can undo all of the formatting, but I have an issue when I try to reapply the conditional formats. Here's my code for that part:

'BORDER ALL CELLS IN ROW WHEN A VALUE EXISTS IN FIRST COLUMN

Range("A1:J14").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT($A1="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False

'HIGHLIGHT "BOOM6" IN GREY AND CHANGE FONT TO GREY

Range("A1:A14").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A1=""Boom6"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

And here's the issue: The first part of the code (i.e., border all of the cells in a row when the first column isn't blank) works fine. The last part of the code (i.e., shade the interior of the cell in the first column light grey when the cell contains "Boom6") works fine. But the middle part (i.e., change the font of the cell in the first column to light grey when the cell contains "Boom6") doesn't work. Instead, all of the cells in the range A1:J14 have the font changed to light grey. Essentially, what it's doing is applying the font rules to the first conditional format setup by the macro ("=NOT($A1="""")").

Any thoughts? I'm really struggling on this...

 

[Norie]

That's the condition you've set as first priority so it must be overriding the other formatting in some way.

 

[metzgek]

I thought that would be the case, too. But if that's true I don't understand why the part of the code that fills the cells works appropriately, but the part that changes the font applies it to all.

 

[Norie]

I can't see why either, and I've just noticed you are setting the 2nd condition as the first priority.

Another thing I noticed is that in the code 1 is used for the index for both sets of conditional formatting.

Perhaps the actual formatting rather than the conditions is getting mixed up.

Have you checked the conditional formatting after the code has been run?

 

[metzgek]

Thanks for the reply, Norie. I have noticed that the code 1 is used for both. Frankly, I'm not sure of the proper way to use this value. When I change Selection.FormatConditions(1).Font to Selection.FormatConditions(2).Font, nothing changes - the problem still persists. When I change Selection.FormatConditions(1).Interior to Selection.FormatConditions(2).Interior, it applies the grey cell FILL to all of the cells in the range (A1:J14).

I wonder if there's an issue with setting everything first priority? I'm really at a loss. If you could help any more I would hugely appreciate it. There's a copy of a sample copy of this problem at this link http://www.excelforum.com/excel-work...ml#post2637143