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

#### vba_newbird

##### New Member
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
You can use Conditional Formatting, but there is a maximum of 3 conditions. How many conditions do you have?

#### vba_newbird

##### New Member
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
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``````

#### vba_newbird

##### New Member
It worked!

Thanks Andrew. Your prompt help is greatly appreciated.

Replies
1
Views
377
Replies
2
Views
647
Replies
0
Views
609
Replies
5
Views
473
Replies
7
Views
613

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

### 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.

### Which adblocker are you using?

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

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