Conditional Format Macro Issues

metzgek

New Member
Joined
Nov 2, 2011
Messages
6
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...
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That's because you are using the first format condition:

Code:
With Selection.FormatConditions(1).Font

try changing the 1 to 2.
 

metzgek

New Member
Joined
Nov 2, 2011
Messages
6

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
metzgek

Please don't duplicate posts.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top