Help Creating VBA Please - Linking Colour via Name in Sheet 1 to Sheet 2

[HelpPls21NZ]

Hi,

I need some help please.

I have Sheet 1, which is a colour index sheet.
Column A - Colour Name
Column E - RGB Colour
Column G - R Value
Column H - G Value
Column I - B Value

I found a VBA formula online and managed to get the colour to show in Column E.

Now, I have Sheet 2.
Which contains Colour Combos.

Column A - Colour Name
Column B - Combo 1A
Column C - Combo 1B
Column D - Combo 2A
Column E - Combo 2B
Column F - Combo 3A
Column G - Combo 3B

What I'd like to be able to do - Find a macro that can do this

1. Find the name on Sheet 1.
2. Get the associated colour ie Column E
3. Recognise the colour names in the columns of Sheet 2.
4. Replace the background with the associated colour
5. Font to be black or white depending on how dark the background colour is

I don't know how to code so thus have to ask for help here.

I have attached screenshots.
Could you help?

Any assistance is much appreciated.

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub HelpPls()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 4).Interior.Color
      Next Cl
   End With
   With Sheets("Sheet2")
      For Each Cl In .Range("A2:G" & .Range("A" & Rows.Count).End(xlUp).Row)
         If Dic.Exists(Cl.Value) Then
            Cl.Interior.Color = Dic(Cl.Value)
            Cl.Font.Color = TextColorToUse(Cl.Interior.Color)
         End If
      Next Cl
   End With
End Sub
Function TextColorToUse(BackColor As Long) As Long

'  This function returns the color to use for
'  text to make it readable on a dark background
'  Code by Rick Rothstein
  Dim Luminance As Long
  Luminance = 77 * (BackColor Mod &H100) + _
              151 * ((BackColor \ &H100) Mod &H100) + _
              28 * ((BackColor \ &H10000) Mod &H100)
  '  Default value of TextColorToUse is 0-Black, set
  '  it to White if the Luminance is less than 32640
  If Luminance < 32640 Then TextColorToUse = vbWhite
End Function

 

[HelpPls21NZ]

Hi & welcome to MrExcel.
How about

Sub HelpPls()
   Dim Cl As Range
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 4).Interior.Color
      Next Cl
   End With
   With Sheets("Sheet2")
      For Each Cl In .Range("A2:G" & .Range("A" & Rows.Count).End(xlUp).Row)
         If Dic.Exists(Cl.Value) Then
            Cl.Interior.Color = Dic(Cl.Value)
            Cl.Font.Color = TextColorToUse(Cl.Interior.Color)
         End If
      Next Cl
   End With
End Sub
Function TextColorToUse(BackColor As Long) As Long

'  This function returns the color to use for
'  text to make it readable on a dark background
'  Code by Rick Rothstein
  Dim Luminance As Long
  Luminance = 77 * (BackColor Mod &H100) + _
              151 * ((BackColor \ &H100) Mod &H100) + _
              28 * ((BackColor \ &H10000) Mod &H100)
  '  Default value of TextColorToUse is 0-Black, set
  '  it to White if the Luminance is less than 32640
  If Luminance < 32640 Then TextColorToUse = vbWhite
End Function

Thank you for your swift reply.

I added that code to Sheet2 but nothing happens.
Should I add it to Sheet1 instead?

Sorry.. but total noob here.
Coding is way beyond my scope :P

Thanks

 

[Fluff]

You need to put it in a standard module, rather than a sheet module & you run it by using Alt F8, select the macro & click "Run", or you can assign it to a button.