CODEWITHSUNDEEP
excelvbargb
Michael Walker
Michael Walker

Reputation: 17

Change colour of selected cell using values in cells based off the selected cell

I have a macro to colour a cell based on the RGB values.
I have cells A1, B1, and C1 for the RGB values.

Sub FillWithRBG()
    Range("D1").Interior.Color = RGB(Range("A1").Value, Range("B1").Value, Range("C1").Value)
End Sub

I can select anywhere in the worksheet, run the macro and only cell D1 will change colour.

I want to select cell D2, run the macro and Cell D2 changes colour based off values in A2, B2, and C2.

I imagine I need to set the active cell with a reference, then 1, 2 and 3 will be offset from the selected cell.
An added bonus would be that the macro can only run in the D column to prevent errors.

Upvotes: 1

Views: 170

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

This would be a simple approach

  • In the code Me. is refering to the current sheet

  • You have to place this code in the sheet's module

enter image description here

  • Columns in VBA are noted by numbers (so column A is referenced as column 1)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    ' Prevent change if changed values are no in these columns (1,2,3) numbers are equivalent to A, B, C
    If Target.Column > 3 Then Exit Sub
    
    ' Change color of D column (4 = D)
    Me.Cells(Target.Row, 4).Interior.Color = RGB(Me.Cells(Target.Row, 1).Value, Me.Cells(Target.Row, 2).Value, Me.Cells(Target.Row, 3).Value)
    
End Sub

  • If you want it to work only if you're changing one cell at a time add these lines:

      ' Prevent change when more than one cell is changed
  If Target.Cells.Count > 1 Then Exit Sub

Upvotes: 1

Related Questions