Change cell fill color automatically, w/in specific ranges


New Member
Nov 7, 2005
Hi - great web site - will be buying some of your stuff. I am not a programmer, but know enough to get into trouble. I have a spreadsheet with several worksheets within, and my issues are:

OK: I have a range, 19:27, columns B-M. I wish to assign a specific color to denote a grade level (A-F). Color index numbers are 4,45,6,38,3 respectively. The values within each cell vary in range: B19 may be from 0-9 whereas C19 may be from 0-25, etc. The range is pre-adjusted with a weight factor, which can vary per user.

In general, users predetermine their weighting for all cells in the B-M columns (in another section). Then, users input raw data into one worksheet. This data is accessed by another worksheet with the B-M columns, per formulas. This gives a value, which is then totalled along with the rest of the row values, to give a score.

For any specific cell, how do I change the cell's fill color automatically? I have tried various VBA scripts - they seem to work once, not in "real-time". If I change a weight, the corresponding dependant cells do not adjust to another color.

I can forward the spreadsheet (or copy portions) to help illustrate - I did not see if I can do this for this post. My email is

Here is my attempt to copy and paste the worksheet section: well, that didn't work...would help if you can see what I am discussing (although this cell fill color topic has been beaton to death, has it not?).

Thanks for any and all help!

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
All sorts of variables/possibilities here. I cannot see how you fix the grade for each cell. Depending on how you do this could determine how you access each one. The following code shows just the principles involved.

If several changes occur at once (or to initialise) it is not a bad thing to check each cell in the range. Below are 2 types of loop that will do this. I have left out the code that does the change. Same as that in the main code. You could have a separate routine to do this.

The main code as shown runs every time a change is made to the value of a cell in the worksheet. It only checks the single cell that has been changed and acts accordingly if it is withing the defined area. Changes the colour depending on cell contents being "A","B","C","D" or "E".

Copy/paste the code into the module reached by right clicking the sheet tab and "View Code".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim MyRange As Range
    Set MyRange = ActiveSheet.Range("B19:M27")
    Dim MyCell As Range
    Dim MyGrade
    Dim MyCellColour As Integer
    Set isect = Intersect(Target, MyRange)
    If isect Is Nothing Then Exit Sub
    Set MyCell = Target
    '- determine grade/colour
    MyGrade = UCase(MyCell.Value)
    Select Case MyGrade
        Case Is = "A"
            MyCellColour = 4
        Case Is = "B"
            MyCellColour = 45
        Case Is = "C"
            MyCellColour = 6
        Case Is = "D"
            MyCellColour = 38
        Case Is = "E"
            MyCellColour = 3
        Case Else
            MyCellColour = xlNone
    End Select
    '- set colour
    MyCell.Interior.ColorIndex = MyCellColour
'    '----------------------------------------------
'    '- each individual cells in the range
'    For Each MyCell In MyRange.Cells
'        '- determine grade/colour
'        '- set colour
'    Next
'    '---------------------------------------------
'    '- alternative loop method using row/column
'    For rw = 1 To 9
'        For col = 1 To 12
'            Set MyCell = MyRange.Cells(rw, col)
'            '- determine grade/colour
'            '- set colour
'        Next
'    Next
'    '---------------------------------------------

End Sub
Upvote 0
Reply to post

Hi Brian - thanks for the post - I'll take a closer look at your suggestions later. I've created some code that runs upon change and seems to be working right now - but I'll need to enhance it for another section of the spreadsheet.
Upvote 0

Forum statistics

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