Forum Discussion
conditional formatting based on colour of another cell
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 othe...
thanks for the document. It's all good stuff but I don't think it answers my query. NikolinoDE
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)
- 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- 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' ) 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)
