Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: TomTS/Dave - bit more help on permanent highlighting, plz

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

    Default

    Morning, I have the following piece of code that highlights rows depending on various conditions. This is an extension upon your recommendation of putting the code in Private Sub Worksheet_Calculate() to allow the process to run continuously without user interference.

    The problem is the value in D1 is always moving and when the condition is met the row is highlighted but as soon as the value in D1 changes, the highlight clears - I don't want this. I want the highlight to remain. Can you help please? Here's the code:

    Private Sub Worksheet_Calculate()

    Dim lLastRow As Long, Data1, ColE, ColG, ColH, COlI, lRow As Long
    Dim shtThis As Worksheet, bThisRow As Boolean
    Data1 = Cells(1, 4).Value

    Set shtThis = ActiveSheet

    With Cells(5, 1).CurrentRegion
    lLastRow = .Rows.Count + .Row - 1
    End With

    For lRow = 5 To lLastRow
    ColE = Cells(lRow, 5).Value
    ColG = Cells(lRow, 7).Value
    ColH = Cells(lRow, .Value
    COlI = Cells(lRow, 9).Value

    bThisRow = False

    Select Case ColG
    Case "DNT"
    If ColH = Data1 Or COlI = Data1 Then _
    bThisRow = True
    Case "OT"
    If ColE = Data1 Then _
    bThisRow = True
    Case "RKO", "KO"
    Select Case ColF
    Case "C"
    If COlI = Data1 Then _
    bThisRow = True
    Case "P"
    If ColH = Data1 Then _
    bThisRow = True
    End Select
    End Select

    With shtThis.Rows(lRow)
    If bThisRow Then
    With .Interior
    .ColorIndex = 3
    .Pattern = xlSolid
    End With
    Else
    With .Interior
    .ColorIndex = xlNone
    .Pattern = xlNone
    End With
    End If
    End With

    Next


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

    Default

    Sorry guys, ignore the last msg. Took the else statement off - all ok now.

    Thanks anyway.

Some videos you may like

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
  •