Cndtnl Fmt a1 based on a2 based on a3Closed

Question

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         

Configuration: Windows 7 / Internet Explorer 8.0

venkat1926 · Answer

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

T Bennett · Answer

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

Cndtnl Fmt a1 based on a2 based on a3

2 responses

Similar discussions

Subscribe To Our Newsletter!