Colouring a row based on a number within a cell on the row

vba_newbird

New Member
Joined
Jun 5, 2007
Messages
19
Hi all,

Will appreciate if anyone could help me with this. Thanks in advance :)

Let's say I have with me some data with Column A containing certain numbers for example, from 1 to 10. Now, I'm given a task to colour the row with a specific colour based on the number in Column A. For example, if cells A3, A5 and A9 contain 3, then rows 3, 5 and 9 will b coloured red. If cells A1 and A6 contains 5, then rows 1 and 6 will be green etc. Is there a way to do this using VBA and some shortcut keys? If there's a way, then my work life here will become much more simpler, else we will have to colour the rows manually. So, I would really appreciate any replies that can be of help here. :D

Thank you so much for any replies.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use Conditional Formatting, but there is a maximum of 3 conditions. How many conditions do you have?
 

vba_newbird

New Member
Joined
Jun 5, 2007
Messages
19
You can use Conditional Formatting, but there is a maximum of 3 conditions. How many conditions do you have?
Definitely more than 3 and will increase if the need arises.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Right click the sheet tab and choose View Code. Paste this into the window on the right:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Select Case Target.Value
        Case 3
            Target.EntireRow.Interior.ColorIndex = 3
        Case 5
            Target.EntireRow.Interior.ColorIndex = 10
        Case Else
            Target.EntireRow.Interior.ColorIndex = xlNone
    End Select
End Sub

Press Alt+F11 to return to your worksheet and try it out. You can add more Cases for other values/colours.
 

Forum statistics

Threads
1,181,606
Messages
5,930,869
Members
436,764
Latest member
avalladarez

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
Top