+ Reply to Thread
Results 1 to 2 of 2

Conditional Format Macro Issues

  1. #1
    Registered User
    Join Date
    08-16-2011
    Location
    Denver, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional Format Macro Issues

    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="""")").

    I attached a workbook that has an example of the problem.

    Any thoughts? I'm really struggling on this...
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: Conditional Format Macro Issues

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1