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

[vba_newbird]

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.

 

[Andrew Poulsom]

You can use Conditional Formatting, but there is a maximum of 3 conditions. How many conditions do you have?

 

[vba_newbird]

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]

Right click the sheet tab and choose View Code. Paste this into the window on the right:

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.

 

[vba_newbird]

It worked!

Thanks Andrew. Your prompt help is greatly appreciated.