VBA - Conditionally Change Cell Colour

[parsesdata]

I am struggling to get some VBA code to run properly (or even run at all, frankly) - it keeps flagging errors, and no matter what I try to adjust it, it will not execute. I have successfully tinkered with code in the past, but this is the first time I've attempted to write something more-or-less from scratch with only basic sections pulled off the internet (as I could not find anything designed to do this sort of thing), so I may be missing something quite fundamental.

The intent is to change the colour of cells in a table based on whether the value of the first column exceeds the number in the row header. I have provided a snapshot of my spreadsheet for reference:

\1

If the value in column C is less than or equal to the value in row 1, the corresponding cell colours in D2:H16 should match the colour in column J. If the value is greater, the cell should be light grey. I have a chart with formatting tied to the cell colours, so I need to change the colour of the cell itself, not apply a conditional formatting rule.

Here is my attempt at coding:

Option Base 1

Sub ColourCells()

Dim ShadedCells As Range
Dim ColumnX As Range
Dim Score As Range
Dim Colours As Range

Set ShadedCells = Range("D2:H16")
Set ColumnX = Range("D1:H1")
Set Score = Range("C2:C16")
Set Colours = Range("J2:J16")

Dim R As Long
Dim C As Long
For R = 1 To 15
    For C = 1 To 5
        If ColumnX.Row(C) > Score(R) Then
        ShadedCells(R, C).Interior.ColourIndex = Colours(R).Interior.ColourIndex
        Else
        ShadedCells(R, C).Interior.ColourIndex = RGB(242, 242, 242)
        End If
    Next C
Next R

End Sub

Any help would be very much appreciated!

[skywriter]

Please use code tags when posting code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[parsesdata]

Thanks for the tip!

I'm not sure why the image isn't showing. I'll try to fix that as well...

[FDibbins]

Hi, welcome to the forum

You dio know you can do this with regular CF, right?

[parsesdata]

Thanks for the welcome! I've been playing with this for a while, with no success, and figured a different strategy might be in order.

Conditional formatting was absolutely my first thought, and I did try it. The problem is that the code I am using to format the chart based on this data uses "Interior.Color" to get the cell colour - that returns the base colour of the cell, not the colour applied by conditional formatting. Looking at various articles online, it seems like it would be much easier to change the cell colours with VBA than to identify the colour applied to each of the cells by conditional formatting. If it would only work...

[skywriter]

It would be helpful if you would actually post a spreadsheet, we can then use your data example and the colors you want. Please also put a sample of the grey color you want.

If the value in column C is less than or equal to the value in row 1

Which value, you have 5 numbers in row 1?

What is the significance of the 1's and in one case 2's in the colored rows?

Why do the color bands only go part way across some of the rows?

[parsesdata]

The values in the coloured / shaded cells feed into a donut chart - they just define the size of the slices, and are not important to the VBA conditional formatting.

The idea is that if the value in the top row (1-5) is less than or equal to the value in the left hand column (ranking from 1-5), the cell at the intersection of that row and column gets assigned the accent colour from the corresponding row in column J. Think of it like a bar chart - if it says "4" in the left hand column, I want a coloured bar that is 4 cells long. The remaining cells are RGB(242,242,242), per the code.

[skywriter]

Okay fine. How about the sample sheet?

[parsesdata]

VBA - Colour Coding Cells.xlsm

See attached. It really isn't anything more than what I had posted previously, but hopefully a sheet to manipulate will get some more discussion going.

I also tried using direct cell references, which also did not seem to work. Here is the code:

Option Base 1

Sub ColourCells()

Dim R As Long
Dim C As Long
For R = 2 To 16
    For C = 4 To 8
        If Cells(1, C).Value > Cells(R, 4).Value Then
        Cells(R, C).Interior.Colour = Cells(R, 11).Interior.Colour
        End If
    Next C
Next R

End Sub

Thanks again for your help!