Conditional Formatting - Let user determine the colors

[Teun Ham]

I want to apply a conditional format where the user can determine the colors to be used.

Thing is, the users all want to apply their own colors, and I don't want to create a dozen spreadsheets

So I was thinking about a simple grid where the users can enter the RGB values to "choose" the colors they want to use:
___R___G___B
1__255_0___0
2__0___255_0
3__0___0___255
4__120_120_120

These colors (in this case 1=red, 2=green, 3=blue and 4=gray) should then be applied to the actual cells that require the conditional formatting.

I would LOVE to create this without any VBA (so I can keep the spreadsheet "macro-free"), but if it's impossible without VBA a solution with VBA would be nice too.

Does anyone know if this has been "requested" before?

Thanks!

 

[Teun Ham]

I have managed to make a Proof Of Concept with VBA:

Using the "Worksheet_Change" event I have captured the event if one of the "RGB values" have changed.

I then use...

Dim oRange As Range
Set oRange = ThisWorkbook.Names("TARGET_RANGE").RefersToRange
        
oRange.FormatConditions.Delete
Dim i As Integer
For i = 1 To 5
   Call oRange.FormatConditions.Add(xlCellValue, xlEqual, i)
   oRange.FormatConditions(i).Interior.Color = ThisWorkbook.Names("COLOR_" & i).RefersToRange.Interior.Color
Next i

..where "COLOR_1/2/3/4/5" is a named range which shows the RGB value.

Proof Of Concept works, I can also use formulas to define the conditional format, so now I only have to apply this to the actual worksheet.

If anyone knows a way without VBA, let me know!

 

[Teun Ham]

A second solution which is maybe more appealing is to use ColorIndex in the Conditional Formatting...then change the color of those ColorIndexes

ActiveWorkbook.Colors(2) = RGB(223, 220, 248)

 

[Teun Ham]

It's even easier than I thought...

I have 5 named ranges, "COLOR_1"..."COLOR_5". When a user selects one of these named ranges, a colorpicker will be shown. The user can then select a new color. The ColorIndex of the Workbook will then be updated to the selected color. Any conditional formatting which uses the ColorIndex will be updated instantly!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Integer
    For i = 1 To 5
        If Not Intersect(Target, ThisWorkbook.Names("COLOR_" & i).RefersToRange) Is Nothing Then
            Application.Dialogs(xlDialogPatterns).Show
            ActiveWorkbook.Colors(i) = ThisWorkbook.Names("COLOR_" & i).RefersToRange.Interior.Color
        End If
    Next i
End Sub

Hopefuly this code can help someone else too!