Excel Macro Help – .ThemeColor = xlThemeColorDark1
The spreadsheet I'm working on pulls data from an oracle database and automatically populates cells for which there is no data with "999999." I am attempting to use an excel macro to essentially gray-out all cells that contain this value with the intention of saving users the time it takes to apply conditional formatting to the cells manually each time. I used Excel's "record macro" feature to arrive at the code below, but I get an "Application-defined orobject-defined error" each time. Does anyone know why I continue to get the error and how I might fix the issue in the code below? Thanks in advance for any help.
Software/Hardware used:
Sub gray()
'
' gray Macro
'
'
Cells.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=999999"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub Software/Hardware used:
ASKED:
March 11, 2009 6:47 PM
UPDATED:
March 13, 2009 7:09 AM
Answer Wiki:
Instead of a Macro you can perform in these steps in Excel 2007.
1) Click on cell A1
2) While holding Shift & Control down, click on End button. (The idea is to select the entire worksheet).
3) Now on Home Tab Click Conditional Formatting
4) On Highlight Cell Rules ... .select Equal To
5) On Format Cells that are EQUAL TO ... Type 999999; then select the Format/Color from the Drop Down list. Or select Custom & then select the Gray color that you wish.
Good luck.
I cut and pasted your macro into VB Editor in Excel 2007, tidied up indents etc and assigned it to a hot key It worked perfectly!!
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
