Colouring cells based on other cells values.

TobyMace

New Member
Joined
Jun 10, 2018
Messages
5
Hi all,

I'm trying to achieve:

If the value in column A is equal to cell F8 then fill the colour (English spelling sorry!) of the cell with the RGB values of G8 (contains value for R), H8 (contains value for G), I8 (contains value for B). Plus colour the text with the RGB values of J8, K8 and L8 (will contain RGB values respectively).
Then I'll have F9 etc. etc.
Could someone please help as to how I would "word" or "format" that in VBA.

Thanks in advance!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi & welcome to MrExcel
How about
Code:
Sub AddColour()
   Dim i As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).row To 2 Step -1
      If Range("A" & i).Value = Range("F" & i).Value Then
         With Range("A" & i)
            .Interior.Color = RGB(Range("G" & i), Range("H" & i), Range("I" & i))
            .Font.Color = RGB(Range("J" & i), Range("K" & i), Range("L" & i))
         End With
      End If
   Next i
End Sub
 
Upvote 0
Hi Fluff,

Thanks for your reply! I would imagine it's down to me not explaining my situation very well but it doesn't seem to work.
Could you talk me through what each line is doing on your code above and I may be able to adjust/fill in the blanks?

Thanks again.
 
Upvote 0
It works on Cell A1 but nothing below that.
I almost need A1 to lookup up its value in column F and then colour with the corresponding RGB values in the latter columns.
 
Upvote 0
Having re-read your op I think I've misunderstood what you want.
How about
Code:
Sub AddColour()
   Dim i As Long
   Dim Fnd As Range
   Dim Cl As Range
   
   For Each Cl In Range("F8", Range("F" & Rows.Count).End(xlUp))
      Set Fnd = Range("A1")
      For i = 1 To Application.CountIf(Range("A:A"), Cl.Value)
         Set Fnd = Range("A:A").Find(Cl.Value, Fnd, , xlWhole, , , False, , False)
         Fnd.Interior.Color = RGB(Cl.Offset(, 1), Cl.Offset(, 2), Cl.Offset(, 3))
         Fnd.Font.Color = RGB(Cl.Offset(, 4), Cl.Offset(, 5), Cl.Offset(, 6))
      Next i
   Next Cl
End Sub
 
Upvote 0
You da man Fluff! Works a treat!
Now I'm going to be even more of a pain! Would this code be editable so that the cells in columns B, C, D and E are also coloured?
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top