Setting a Cell Colour using a formula

[Mortimer]

Hi, All,
My question appears to be simple, but I cannot find out how to do this:
I want to conditionally format some cells, using 2 alternate colours - but I want the user to be able to choose the 2 colours to use.
i.e. I want: "if the row no. is odd, then make the cell colour the same as $D$10; If the row no. is even, then make the cell colour the same as $D$11"
Also, I want to do this using formulas and not VBA if at all possible. I can do it in VBA, but that would require the user to explicitly start the process, I think? I want it to be dynamic.
Can anyone help please? It seems to be impossible, but I think this must be a common request.

Thanks a lot in advance,
Mortimer

 

[General Ledger]

Here may be a piece of the answer. Using Conditional Formatting, you can use the formula =ISODD(ROW()) to determine if the row number is odd. A true answer will apply the selected formatting.

Now the hard part, how do you get Conditional Formatting to use a color identified by the user? ? ?

 

[Mortimer]

Yes that is precisely the problem - I'm fine for calculating which cells need to be assigned either colour (It's actually not as simple as I originally said, i.e. not odd /even rows, but I was struggling to explain what the real problem was!)
tbh I find it hard to believe that this isn't a widely requested feature, but then there are some parts of Excel which are a long way behind the majority of the program.
Thanks anyway, here's hoping....
Mortimer

 

[mikerickson]

In general, setting cell colors without VBA, is a common request, but your application is unusual.

Unfortunatly, you do need VBA, but the "explicit" starting of the process doesn't need to be hard.

Lets assume that you have a routine that reads the colors of D10 and D11 and sets the conditional formatting on the cells you want. Call that sub SetCFColors and put it in a normal module.
Then you could use a SelectionChange event like this in the code module for the sheet in question.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("D10:E12")) Is Nothing Then
        Call SetCFColors
    End If
End Sub

The way that the user would initiate the change in colors would be to select D10 (or D11) change it to the color they want and then press Enter or Tab.

 

[mikerickson]

About why Excel doesn't have features like this. Primarily its because color is a poor way to represent data.
Your problem is unusual, you are using color properly, to highlight written data. Your goal is to avoid having to train your users on how to use the Conditional Formatting feature to customize their worksheet.

The improper use of color, color as data, is that the correspondences from color to meaning isn't very clear.
For example, one might want a workbook where a the data in a red cell is "very, very important" and the data in a green cell is "absolutely not important" and that the importance of calculated values would depend on the importance of the precedents.

The difficulty with this approach is that while Red>Green is pretty culturally intuitive. What about other colors?
Is Red > Yellow > Green > Blue or is Red > Blue > Yellow > Green or ....

Color as data is super dependent on the programming, highly sensitive to user training.
Its much more intuitve and clear to use a companion cell with importance 1,2,3 or 4 than color. (And easier to remember when you haven't seen that particular workbook in a year.)

That is why as a general thing, with any software, color as data is not a good approach.
Which (my guessing) is why MS didn't explore color controlled by functions any more than it did with CF.

 

[Mortimer]

First of all, thanks to evryone for the constructive replies - a vast improvement on the usual forum conversations The spreadsheet has >9,000 rows of data, and each part number (which could be multiple rows of data) is assigned to a user (I think there are ~6 users). The colour is purely to help the user to read the data, so the preferred colours differ with the user. They use autofilter to show only the parts assigned to them, so I need to then apply the alternate shadings after the filter has been applied. I looked last night and arrived at the conclusion (as given above) that VBA is the only option. Being a relative novice at VBA, I haven't worked out how to get the colour of a cell, can anyone help with this?
Thanks in advance,
Mortimer

 

[mikerickson]

With ActiveCell
    MsgBox "Cell " & .Address & " has color index " & .Interior.ColorIndex
End With

 

[MARK858]

If you are going to need VBA could you not display the colorindex and get the user to select the colour number, something like below?
I have assumed your cells to colour are in Column A

Sub ColorRows()
    Dim c As Range
    Dim ColInd(0 To 1) As Long
    Dim i As Long
    Dim Rng As Range
    Dim j As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

 For i = 0 To 56
        Cells(i + 1, 12).Interior.ColorIndex = i   'Change 12 to an empty column
        Cells(i + 1, 12).Value = "[Color " & i & "]" 'Change 12 to an empty column
    Next i
    Application.ScreenUpdating = True
    ColInd(0) = Application.InputBox("Insert colour #1.", 1)
    ColInd(1) = Application.InputBox("Insert colour #2.", 1)
    j = 0
    Columns("L:R").Delete
    Application.ActiveSheet.UsedRange
    Set Rng = ActiveSheet.UsedRange
    Rng.Interior.ColorIndex = ColInd(j)
   For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
        c.Interior.ColorIndex = ColInd(j)
        j = 1 - j
    Next c
done:
    Application.Calculation = xlCalculationAutomatic
End Sub