hightlight row(s) based on value

Trevor3007

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
675
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:
Sort by date Sort by votes
Another way...

Code: 
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
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Zenwood said:
Another way...

Code: 
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
Click to expand...


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
 
Upvote 0
Have you tried the macro I suggested in Post #10 ?
 
Upvote 0
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:-

[TABLE="width: 321"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Build Completed[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Build Started[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]Device Not Received[/TD]
[TD]light blue[/TD]
[/TR]
[TR]
[TD]Device With Build Engineer[/TD]
[TD]no colour required

[/TD]
[/TR]
[TR]
[TD]Emailed Requested For SCCM Check [/TD]
[TD]light purple

[/TD]
[/TR]
[TR]
[TD]Swapped-Out[/TD]
[TD]light red[/TD]
[/TR]
[TR]
[TD]Desktop UAD - On Hold ATM[/TD]
[TD]orange[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
I am sorry mumps... i am unsure if I have seen or not? And I am unable to locate #10 ?
 
Upvote 0
At the bottom right where it says "Page 2 of 2", click on Page 1.
 
Upvote 0
For new colors, try:
Code: 
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
 
Upvote 0
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.


Code: 
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
 
Upvote 0
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


 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Specific VBA Function
Replies
0
Views
252
EAlexWolfe
E
D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
913
DH937
D
P
  • Question Question
Moving rows to different sheets based on drop down.
2
Replies
12
Views
987
purevega
P
T
  • Solved
Hiding A row based on second work sheet based on Yes / No dropdown on first work sheet help....
Replies
5
Views
610
James006
James006
K
  • Question Question
Macro help! Move a row of data to separate sheet based off the value in column M and delete from original sheet
Replies
1
Views
551
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top