Cell color fill

[rjplante]

I have a spreadsheet that I have conditional formatting set up so that when a user selects a name in column C, that row from column A to column Q is highlighted a particular color. It works pretty slick, but as rows are inserted, deleted, or sorted, the formulas for the conditional formatting seem to get jumbled up and don't function as well. I was wondering if there was a better way to do this via VBA. I have a table (included below) in cells CB188:CF193 which contains the users name (column CB), a color example (CC), and then the values for RGB color code, R-(CD), G-(CE), and B-(CF). I have a macro that will change the color of the rows based on the user selected, and I would have to write additional lines for each user (6 in all). Is there a way to reference the table for the user and RGB color matrix, so that I would not have to specify the cell references for each color I need? Sort of like a VLOOKUP formula in VBA.

The end goal is to move away from conditional formatting and transition to VBA control. When rows are then sorted, inserted, or deleted, the VBA remains intact. If the user name in col C changes, the color for that row changes. Coloration of the row remains when the rows are sorted. If this is not a good idea, and conditional formatting is better, let me know and I will maintain what I have in place now.

Thanks for the support,

Robert

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NameRange As Range
    Dim NameRow As Long
    Dim ColorGus As Long

NameRow = ActiveCell.Row

ColorGus = RGB(Range("CD193").Value, Range("CE193").Value, Range("CF193").Value)

Set NameRange = Range("C3:C255")

    If Not Application.Intersect(NameRange, Range(Target.Address)) Is Nothing Then
              
        If Range(Target.Address) = "Gus" Then
        
            Range("A" & NameRow & ":Q" & NameRow).Interior.Color = ColorGus
        Else
            Range("A" & NameRow & ":Q" & NameRow).Interior.ColorIndex = 0
        End If

    End If
   
End Sub

TW Schedule Upgrade 01-DEC-2020.xlsm
	CB	CC	CD	CE	CF
181			R	G	B
182	Initial Draft:		255	250	150
183	Ship Date:		215	240	200
184	Final Draft		255	215	215
185	Draft SDS		200	215	240
186	Product Report		255	220	255
187	Fill		217	217	217
188	Fred		255	215	255
189	Gary		255	220	165
190	Jon		200	245	200
191	Duke		200	175	230
192	Bill		255	255	190
193	Gus		190	240	255
Job List

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   
   If Not Application.Intersect(Target, Range("C3:C255")) Is Nothing Then
      Set Fnd = Range("CB18:CB193").Find(Target, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Intersect(Target.EntireRow, Range("A:Q")).Interior.Color = Fnd.Offset(, 1).Interior.Color
      Else
         Intersect(Target.EntireRow, Range("A:Q")).Interior.ColorIndex = xlNone
      End If
   End If
End Sub

 

[rjplante]

Thanks Fluff, that works awesome!!!

 

[Fluff]

You're welcome & thanks for the feedback.