+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting - records ok, but doesn't work properly when played back

  1. #1
    Registered User
    Join Date
    07-12-2005
    Location
    Canberra, Australia
    MS-Off Ver
    2007
    Posts
    43

    Conditional Formatting - records ok, but doesn't work properly when played back

    I recorded the following code, but when it is played back the first condition works perfectly, but the second condition seems to be applied to the same range as the first condition. Can anyone tell me what I need to change? Thanks

    ' conditional format 1
    Range("A5:S2000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$N5<>$N4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlTop)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    ' conditional format 2
    Range("N6:R2000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$N6=$N5"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Conditional Formatting - records ok, but doesn't work properly when played back

    Does this apply the conditional formatting to the correct ranges?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    07-12-2005
    Location
    Canberra, Australia
    MS-Off Ver
    2007
    Posts
    43

    Re: Conditional Formatting - records ok, but doesn't work properly when played back

    Hi Norie,

    I have attached a sample file with the VBA code and the report that I want the conditional formatting to apply to. I am applying it to the correct range, but on running the macro the second conditional format doesn't apply to the correct area. It is supposed to apply to a smaller range within the range that the first conditional format applies to.

    Column A contains the names of pharmaceuticals including the dosage/qty
    Column N contains the first word of the pharmaceutical name only.

    The first conditional format correctly applies a border from cell A5 to cell S200 when the name in column N changes
    I want the second conditional format to apply a white font from cell A6 to cell S200 if the name in column N is the same as the one above it.

    What it actually does when I run the macro is apply a white font to the first word and it applies this to the range A5 to S200 - i.e. the range that the first conditional format relates to.

    I have manually applied a white font in column N to a few of the cells, to indicate the effect that I am trying to achieve.

    I hope that makes sense.
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 02-28-2014, 07:12 AM
  2. Conditional Formatting doesn't work after copy and insert
    By amarus99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2012, 02:52 PM
  3. Excel 2007 : AND doesn't work in Conditional Formatting
    By cellarir in forum Excel General
    Replies: 2
    Last Post: 03-21-2012, 06:10 PM
  4. Replies: 3
    Last Post: 09-09-2009, 03:35 PM
  5. Replies: 0
    Last Post: 02-10-2005, 02:46 AM

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