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:A14 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...

 

[Andrew Poulsom]

That's because you are using the first format condition:

With Selection.FormatConditions(1).Font

try changing the 1 to 2.

 

[metzgek]

Hi Andrew - I just tried that with no luck. Nothing is different. When I change the 1 to 2 for the "Selection.FormatConditions(1).Interior" it applies the grey shading to all of the cells, similar to what it's doing with the font.

Am I on the right track, though? Any other thoughts how changing that would help? I have a sample version of the file at the following link:

http://www.excelforum.com/excel-worksheet-functions/800180-conditional-format-macro-issues.html#post2637143

 

[Norie]

metzgek

Please don't duplicate posts.