conditional formatting based on colour of another cell

[RuthC2809]

Hi,

Thanks in advanced to anyone who is able to help. 

I am trying to get a cell's colour to inform the text in another cell. For example is B2 is green, C3 will say "a" (and longer text in other scenarios). I have used =IF(CellColour=4,"a","x") but it does not automatically update when i change the colour back to white. 

The idea is staff will be able to right click on the cells which are relevant to the job to turn them green and on another sheet the text will automatically appear (I've already programmed right click to turn cells green). For example B2 in sheet one may say 4 x 4 and if that cell is green then in Sheet 2, cell A2 will say "A 4 x 4 will be needed to access site."

Any help would be much appreciated.

[#2]

@RuthC2809 

Here are some examples in the inserted file to see how many possibilities there are with the conditional formatting.

If you liked this information / answer, please mark it as the best answer and thumbs up :-), so other members of this forum can also benefit from it.

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

[#3]

thanks for the document. It's all good stuff but I don't think it answers my query. @NikolinoDE 

[#4]

@RuthC2809 

Here is an example with VBA.

If double click on cell with left mouse button then red.

If you click right mouse button then green.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbRed
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbGreen
End Sub

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#5]

Hi Nikolino,

I have already done this. What I need to know how to do is to conditional format based on colour of another cell.

Thank you

[#6]

@RuthC2809 

My last try :)).

You only need to change the cell with the text and the color as you wish.

Wish a good day

Nikolino

I know I don't know anything (Socrates)

[#7]

I think what you want is a function like this:
Public Function cellColor(ref)
Application.Volatile
cellColor = ref.Interior.Color
End Function
and then on the worksheet you just say:
=IF(cellColor(A1)=4,"a","x")
BUT
a) the cellColor being returned is a 24bit value (or something like that). If you want the colorindex value you need to change the macro accordingly
b) that (or any) formula will not change based on changing the color of a cell so you have to do some action on the sheet (e.g. change a value) to force the macro to run and update the value. You could also add:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
BUT I HIGHLY CAUTION this as if your sheet has any decent amount of calculations on the sheet it can make the sheet unusable as every click or enter or arrow key will cause a worksheet calculate action to happen!! AND even with this the value will NOT update when you actually change the cell color but only after you move the selection to a different cell (e.g. click 'ENTER' )