Excel 2007 vba if statement fill cell colour based on precious cell colour

Question

please help me, i have no idea how to work with colours in vba coding. am gonna try and explain this as simple as i can.

i have cells from D3 to AH564 that has no values in them and never will. if i change the colour in any of these cells to blue, it must change the cell that is 14 cells next to it(R1C14) to green then again 14 cells blue and again green. just three times. and if i change a cell to green do the same thing.

i have tried several codes found on the site and modified them to my needs but all of them uses values and i dont know where to even start with colours.

background colours are as follows: Blue (RGB) 0, 112, 192 Green (RGB) 146, 208, 80

i want the macro to change the 14th cell to the other colour 3 times.

thanks for all the help.

Answer 1

You have to use the Interior property of the cell, and then:

You can use ColorIndex (to use one of the 56 "preset" colors in Excel):

ActiveCell.Interior.ColorIndex = 36 

Or you can use Color:

Range("A1:A6").Interior.Color = RGB(0,112,192)

You can read up on this at Chip Pearson's excellent article on using colors in Excel VBA

Answer 2

You can have VBA code that gets triggered when a cell is changed.

Formatting a cell to have a different color, though, does not trigger a change event, so just changing a cell fill will not fire up any macro.

Instead of formatting a cell with a fill color, you could enter a number or text into the cell. You can use conditional formatting that will change the color of a cell with a value.

Entering a text or number into a cell will trigger a change event, and that event can easily change the fill color of other cells.

So, set up conditional formatting if a cell contains a value, say, 1. If it's a 1, format it blue with a blue background fill. This way, the number won't be visible.

Then use this macro (right-click the Sheet tab, click "View Code" and paste the code into the code window)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D3:AH564")) Is Nothing Then
        If Target = 1 Then
            Target.Offset(0, 14).Interior.Color = RGB(146, 208, 80)
            Target.Offset(0, 28).Interior.Color = RGB(0, 112, 192)
            Target.Offset(0, 42).Interior.Color = RGB(146, 208, 80)
        End If
    End If

End Sub

It is not clear if the trigger cell can be all over the range or just in column D. The macro can be adjusted to accommodate whatever you define.

Edit:

If you copy and paste a value into multiple cells, then try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
    If Not Intersect(Target, Range("D3:AH564")) Is Nothing Then
        If Target.Rows.Count > 0 Then
            For Each cel In Target
                If cel = 1 Then
                    cel.Offset(0, 14).Interior.Color = RGB(146, 208, 80)
                    cel.Offset(0, 28).Interior.Color = RGB(0, 112, 192)
                   cel.Offset(0, 42).Interior.Color = RGB(146, 208, 80)
                End If
            Next cel
        End If
    End If

End Sub