Cndtnl Fmt a1 based on a2 based on a3

[Closed]
Report
-
 T Bennett -
Hello, I have reduced my question to this simple example. I am trying to set up a conditional format in Cell A1 based on the results in cell A2. Cell A2 is then dependent on Cell A3.

Color in A1 is based on condition =A2=TRUE (green if true, Red if false)

Formula in A2 is =A3=True.

I manually enter one of two formulas in cell A3, either =1=1 or =1=2. a3 result will either be true or false.

I would expect that if the result in A3 is true, than color of A1 to be green and changing A3 formula for a false result would change A1 color to Red. However, color does not change even though the result of A2 does change from True to false or vice-vesa.

Also, if enter the formulas as described for A3 directly into A2, the color of A1 does switch from Green to Red as expected.

I am using Excel 2007
Your help would be appreciated.
Tim

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
A2 has formula
=A3
in A3 you enter the formula either
=1=1
or =1=2

you wan A1 color to change
try this
right click sheet tab and click view code there you copy this event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EditDirectlyInCell = False
If Target = True Then
Range("A1").Interior.ColorIndex = 3
Else
Range("A1").Interior.ColorIndex = 4
End If
Application.EnableAnimations = True
End Sub


now you enter either of the two formulas in A3and see what happens in A1
Thanks, venkat1926

Your macro does work in my simplified test workbook.

I'm thinking the animation line causes excel to continuously scan the sheet looking for changes. Anyway, pretty neat.

I have 20 cells that may require this and they are not contiguous. Some folks are afraid to use sheets with macros created by others and therefore I try to avoid including them. But, if I need to, I will work this into my production workbook.

Update to my original problem:
I found that my colors do change at A1 if, after a change in value at A3, I change the height or width of any row or column to greater or less by clicking between rows while scrolling either up ,down. Something about that triggers the color change. It seems to have to do with the fact that the primary formula that creates the value displayed (in the cell which also contains the conditional format) is derived from a different worksheet.

Found this statement:

" Note When you create a conditional format, you can only reference other cells on the same worksheet; you cannot reference cells on other worksheets in the same workbook, or use external references to another workbook."


But I thought that would only apply to the conditional format rule/fromula, not the display formula. And it's not a problem in my simple test workbook - only my production workbook.

Tim
Also found that my conditiona format colors do update when I change view to different worksheet. This fact makes the problem as it applies to my application a non issue. I enter the affected values on two other worksheets - 2 and 3 and, when I click on sheet 1, they are updated so my users will never know the problem exists.

ALSO NOTE !!!!!! Running the macro posted above will leave your EXCEL application in a state that you can no longer directly edit data in cells. If you don't know how to reset that option, don't run the macro.