Change cell fill color automatically, w/in specific ranges

[chadwick404]

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 chad.morgan@searay.com

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!

 

[BrianB]

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
    '-----------------------------------------------
    '- CHECK CHANGE IS WITHIN THE RANGE
    Set isect = Intersect(Target, MyRange)
    If isect Is Nothing Then Exit Sub
    '-----------------------------------------------
    '- CHECK CRITERION FOR TARGET CELL
    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
'    '----------------------------------------------
'    '- CHECK CRITERION FOR EACH CELL
'    '- 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

 

[chadwick404]

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.