Permanent Highlight using Conditional Formatting
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Permanent Highlight using Conditional Formatting

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a value in D1 (which is constantly/automatically changing), If this value in D1 matches, say, E5, then I want the row to change colour to Orange, permanently.

    Because D1 is changing, the cell does not seem to stay highlighted - is there anyway round this.

    Also, I would like the whole row (from A5 to L5) to be highlighted, but this does not seem to work, eventhough I select all rows when setting the conditional format.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ben


    If you want this change to stick permantely you will need VBA. Right click on the sheet name tab and select "View Code" now paste in this code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Rows(5).Interior.ColorIndex = 46 Then End
    
        If Target.Address = "$D" And Range("D1") = Range("E5") Then
            Rows(5).Interior.ColorIndex = 46
        End If
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Morning Dave,

    I tried your suggestion but it didn't seem to work. I new to VBA, so maybe I overlooked something.

    The only thing is, I want condition to be met automatically, i.e without running a macro, etc.

    Thanks in advance,

    Ben

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ben

    This code will happen automatically as soon as the user types a value into D1 that is = to E5.

    Oops, just looked at the code and it seems I ommited the 1 from "$D$1"

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Rows(5).Interior.ColorIndex = 46 Then End

    If Target.Address = "$D$1" And Range("D1") = Range("E5") Then
    Rows(5).Interior.ColorIndex = 46
    End If

    End Sub


    You will also need to remove any conditional formatting for row 5

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aha! Works fine, when I change cell D1. However, because D1 changes automatically without user interference - the code doesn't seem to work, even though D1 = E5.

    Anymore suggestions?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HELP!

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another thought; can I add a macro to the conditional format and do it that way?

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ben


    Just remove the code:
    "Target.Address = "$D$1" And"

    From the original macro.



  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave,

    Thats fantastic! Thank you very much. I've got the code to highlight when D1 = E5.

    However, if focus is not on the cell/workbook, the highlight does not seem to work - is there a way of getting round this?

    Once again, thanks for your help so far.

    Ben

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Perhaps this code needs to be placed in the Calculate event?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com