Assign a value to a range of cells based on the color of conditional formatting

[JakeP]

Hello all color formatting experts!

I have searched all threads and much online without much luck.

I have a rather complex sheet that automatically formats the color of a range of cells based on a date range in other cells. (creates a Gantt Chart)
If that conditional color is green for example, I'd like to auto assign a numerical value to each of those green cells. (These would be for hours assigned for that day to ultimately create a daily team workload capacity analaysis)
I'd like this to work on an entire desired range of cells and also have the ability to do this for different colors. (if yellow, assign a different numerical value, blue...)

I'd also like this to work on any colors whether they are manually assigned by normal formatting means or by conditional methods.


I assume VBA would be required, considering this would need to evaluate conditional formatting?

Hopefully, this makes sense.

Thank you very much in advance for any support!

JP

 

[6StringJazzer]

This could be done with formulas if you were just concerned about conditional formatting; you could just use the same conditions used in conditional formatting. But for manual formatting, you will probably need VBA. (There may be some obscure macro available for Excel 4.0 macros that can detect fill color, but I've never used it. That could go into a named formula without using VBA.)

In any case, your question is pretty conceptual with no concrete details. To give you actual VBA code to do this we would need know the RGB numbers for each of the colors you are using, and the corresponding values you want to assign, and the applicable cell ranges.

 

[JakeP]

Hi Jeff,
Thanks for the quick reply.

The RGB values and variables can be anything for the VBA example code.
I think I can modify the code to match my own colors. (green= 00,255,00...) and change the corresponding range(s) and values assigned for each color.
Would this be possible?

Thanks, JP

 

[6StringJazzer]

I don't know how you want to use it so here is the code to do it and two ways to call it. I just used Red, Green, and Blue, and values 1, 2, and 3. Modify to suit. You use this one sub for the entire range of cells on the sheet you want to check.

' Call from code for a specific range
Public Sub SetValuesForColor(R As Range)

   Dim Cell As Range
  
   For Each Cell In R
  
      Select Case Cell.DisplayFormat.Interior.Color
     
         Case RGB(255, 0, 0)
            Cell.Value = 1
           
         Case RGB(0, 255, 0)
            Cell.Value = 2
           
         Case RGB(0, 0, 255)
            Cell.Value = 3
           
      End Select
  
   Next Cell

End Sub

' To call from user interface for current Selection
Public Sub SetValuesForColorSelection()
   SetValuesForColor R:=Selection
End Sub

' To call from code
Public Sub InvokeSetValues()
   SetValuesForColor R:=Worksheets("Color Test").Range("A1:D4")
End Sub