Home |
Search |
Today's Posts |
#1
|
|||
|
|||
conditional formatting based on column
In Excel2000, can I have a conditional format based on a formula
reference. For example, I have data in columns A and C. In column B I have formulas that reference either column A or C ( +A15 or +C19). Can Excel use conditional formatting to look at the formula and then format it one way if in column A (red fill) and another in column C (yellow fill). Thanks |
#2
|
|||
|
|||
Hi there,
Think of Conditional Formatting much like that of a circular reference, because that is what it is. You are attempting to give it a True condition unto itself - and if everything lines up (is True) then the condition is met and the format is applied. I'll assume for a minute that we are talking about B15, where the formula in this cell is "=A15". Select B15 Format (menu) | Conditional Formatting | Formula Is .. =B15=A15 Format as desired So you see, it's less complicated to replicate the formula than it is to reference the actual formula statement; which is possible, but (imo) more of a pain than the alternative. Is this what you are asking for? -- Regards, Zack Barresse, aka firefytr "snax500" wrote in message oups.com... In Excel2000, can I have a conditional format based on a formula reference. For example, I have data in columns A and C. In column B I have formulas that reference either column A or C ( +A15 or +C19). Can Excel use conditional formatting to look at the formula and then format it one way if in column A (red fill) and another in column C (yellow fill). Thanks |
#3
|
|||
|
|||
Thanks but your formula doesn't work if both column A and C are equal.
Any other ideas. |
#4
|
|||
|
|||
Add the first condition of
=AND(A1=B1,B1=C1) and format accordingly, then extra conditions of =A1=B1, yellow and =B1=C1, red -- HTH RP (remove nothere from the email address if mailing direct) "snax500" wrote in message oups.com... Thanks but your formula doesn't work if both column A and C are equal. Any other ideas. |
#5
|
|||
|
|||
Well, you can do a couple of things. One would be to use an older technique
that is a little tricky, but very doable. Follow these steps: Hit Ctrl + F3 Name: TheFormula Refers to: =GET.CELL(6,INDIRECT("RC",FALSE)) Hit Add Hit Ok Then select your cell in col B, goto Format (menu) | Conditional Formatting | Formula Is ... =TheFormula="="&ADDRESS(ROW(),COLUMN()-1,4) This will check the formula in the cell in column B, if it says "=A1" (or therow it is in) then the condition is true. Another method contains using a UDF ... Function MyFormula(Optional celRef As Range) If celRef Is Nothing Then Set celRef = Application.Caller MyFormula = celRef.Formula End Function Then use this conditional format for column B... =MyFormula()="=A"&ROW() -- Regards, Zack Barresse, aka firefytr "snax500" wrote in message oups.com... Thanks but your formula doesn't work if both column A and C are equal. Any other ideas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Some help with conditional formatting | Excel Discussion (Misc queries) | |||
Trying to add conditional formatting to every other cell...GOING . | Excel Worksheet Functions | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
Conditional formatting based on text | Excel Worksheet Functions | |||
Conditional Formatting based on month name | Excel Discussion (Misc queries) |