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...
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...
Last edited: