Excel Macro Help – .ThemeColor = xlThemeColorDark1

5 pts.
Tags:
Excel 2007
Excel macros
Microsoft Excel
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.
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
ASKED: March 11, 2009  6:47 PM
UPDATED: August 21, 2013  3:15 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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!!

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following