hightlight row(s) based on value

[Trevor3007]

Hi,

Does any have the 'VB knowhow' to highlight a row(s) that contain a certain value?

The range is c2:c200 & if it contains any of the following keywords:-

base test completed OK - light green\bold

base test completed failure - Light red\bold

base test on test - light yellow\bold

thank you 'kind person' & i look forward to your reply.

KR
Trevor 3007

 

[Zenwood]

Another way...

Sub RowColor_basetest()


Dim Row_ As Integer
Dim Cell_ As Range


Row_ = 2  '    row to start with


     For Each Cell_ In Range(Cells(2, 3), Cells(200, 3)) '    rows & column to sort


          Select Case Cell_
               Case "base test completed OK": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(240, 220, 220)
               Case "base test completed failure": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(220, 240, 220)
               Case "base test on test": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(240, 240, 220)
               'Case Else: Range(Cells(Row_, 2), Cells(Row_, 11)).Interior.Color = RGB(200, 200, 200)
          End Select
     
          Row_ = Row_ + 1
     
     Next Cell_


End Sub

 

[Trevor3007]

Another way...

Sub RowColor_basetest()


Dim Row_ As Integer
Dim Cell_ As Range


Row_ = 2  '    row to start with


     For Each Cell_ In Range(Cells(2, 3), Cells(200, 3)) '    rows & column to sort


          Select Case Cell_
               Case "base test completed OK": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(240, 220, 220)
               Case "base test completed failure": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(220, 240, 220)
               Case "base test on test": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(240, 240, 220)
               'Case Else: Range(Cells(Row_, 2), Cells(Row_, 11)).Interior.Color = RGB(200, 200, 200)
          End Select
     
          Row_ = Row_ + 1
     
     Next Cell_


End Sub

thank you for your code and help which are both very appreciated.

although it works, I found two issues:-

1) i cannot copy n paste ( error message 'excel cannot paste data)
2) when I remove\delete one of the key words & run the macro , the colour remains , I thought it would of just 'blanked filled'


so sorry that this has happened & i look forward to your next post regarding this matter.

MTIA
Trevor3007

 

[mumps]

Have you tried the macro I suggested in Post #10 ?

 

[Trevor3007]

good morning Rick,

thank you ever so much for your help.

Unfortunately ( and without any input from me) there has been a change in the workbook.

the values are now:-

Build Completed	green
Build Started	yellow
Device Not Received	light blue
Device With Build Engineer	no colour required
Emailed Requested For SCCM Check	light purple
Swapped-Out	light red
Desktop UAD - On Hold ATM	orange

<colgroup><col><col></colgroup><tbody>
</tbody>

the values will now be in the range c3:c200.

Prior to finding this change , i did test (based on the previous values) and the only issue I could find is that if a cell that previously had triggered the 'colour' change, if I remove\edit\remove the value , the applicable row remains 'coloured filled' where I thought it would be 'blanked filled'

I am sorry to burden you further & totally understand should you not wish to proceed further with my issue.

Any ways, I thank you & very much appreciate all your help.
KR
Trevor3007

 

[Trevor3007]

I am sorry mumps... i am unsure if I have seen or not? And I am unable to locate #10 ?

 

[mumps]

At the bottom right where it says "Page 2 of 2", click on Page 1.

 

[mumps]

For new colors, try:

Sub ColorRows()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("C2:C200")
        Select Case rng.Value
            Case "Build Completed"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = 4
                    .Font.Bold = True
                End With
            Case "Swapped-Out"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = 3
                    .Font.Bold = True
                End With
            Case "Build Started"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = 6
                    .Font.Bold = True
                End With
            Case "Device Not Received"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = 28
                    .Font.Bold = True
                End With
            Case "Emailed Requested For SCCM Check"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = 38
                    .Font.Bold = True
                End With
            Case "Desktop UAD - On Hold ATM"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = 44
                    .Font.Bold = True
                End With
            Case "Device With Build Engineer"
                With Range("A" & rng.Row).Resize(1, 24)
                    .Interior.ColorIndex = xlNone
                    .Font.Bold = False
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Zenwood]

What are you trying to cut & paste? You can certainly modify my code or Rick's code.

The color remains because nothing triggers the color to change. I modified the code to set everything to white before adding the color. I also add another line to show how you can modify for another phrase.

Sub RowColor_basetest()


Dim Row_ As Integer
Dim Cell_ As Range


Row_ = 2  '    row to start with


Cells.Interior.Color = RGB(255, 255, 255)     '    background to white


     For Each Cell_ In Range(Cells(2, 3), Cells(200, 3)) '    rows & column to sort


          Select Case Cell_
               Case "base test completed OK": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(240, 220, 220)
               Case "base test completed failure": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(220, 240, 220)
               Case "base test on test": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(240, 240, 220)
               Case "Build Completed": Range(Cells(Row_, 1), Cells(Row_, 24)).Interior.Color = RGB(190, 190, 240)
               'Case Else: Range(Cells(Row_, 2), Cells(Row_, 11)).Interior.Color = RGB(200, 200, 200)
          End Select
     
          Row_ = Row_ + 1
     
     Next Cell_


End Sub

 

[Trevor3007]

hi Mumps,

thanks that works great!!!

Only issue is that if a cell that previously had been triggered by the 'colour' change, if I 'blank' any cells in the c2:c200 range , the applicable row remains 'coloured filled' where I thought it would be 'blanked filled'?

are you are to sort...?

MTIA
Trevor3007

 

[Trevor3007]

thank you zenwood,

mumps got in 1st & apart from
the only issue I could find is that if a cell that previously had triggered the 'colour' change, if I remove\edit\remove the value , the applicable row remains 'coloured filled' where I thought it would be 'blanked filled'

all seems good?

Thank you for your help & hope you have a great Christmas .

KR
Trevor3007