Conditional formatting based on text within a formula

[Sarah Jane]

I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formula’s text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

 

[Max]

Imo, easiest way is to use a helper col (say col K) where you would indicate
say, a "2" in the col for such rows (A one-time manual pain, applied
retrospectively for what you already have on the sheet). Then just apply CF
in this manner.

Select the entire sheet (with A1 active)
Under Condition 1,
Formula is: =$K1=2
Format the fill/font, etc to taste > OK out
The above would then conditionally highlight entire rows

 

[Biff]

Hi!

Use Formula Is and just use the same formula but add the /2 to every one.

Whatever Index formula you have in A1:

=A1=INDEX(.....................))/2

I don't know how to word this but maybe you'll get what I'm trying to
describe. If not I'll try again!

Biff

 

[Sarah Jane]

Thanks Max. Believe it or not, I thought of your suggestion shortly after I
posted the question; however, since I run several other macros on this data,
I can't change the column placements by adding another column. For now, I
may try to use column IV since this particular dataset hasn't YET maxed out
the number of columns available in Excel.

I have another workbook with a similar situation that has already maxed out
the number of columns, so if you think of another solution.....

 

[Sarah Jane]

Thanks Biff. I understand what you are saying, and that is a great idea, but
conditional formatting won't allow you to use references to other worksheets
or workbooks. When I tried, Excel gave me an error message.

 

[Biff]

Ooops! I wasn't paying attention with regards to the other sheet.

Is there anything unique about the values that are divided and those that
are not?

Biff

 

[Sarah Jane]

I can't really think of anything unique. The ones being divided represent
common charges. In other words, some charges go to Program A only; some go
to Program B only; and some need to be split evenly between Program A and
Program B. I have manually highlighted (the only thing that shows
uniqueness) the charge number that the formula references in column D, but I
haven't figured out a way to make the conditional formatting criteria look at
another cell's format. Do you know how?

 

[Biff]

You'd have to use a VBA UDF and helper cells to identify the color but that
just defeats the purpose.

You can use a named formula that refers to other sheets in conditional
formatting BUT the references have to be absolute.

=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1,act!$1:$1,0))/2

You could probably rewrite that formula and use an INDIRECT or OFFSET
expression to define $D6 and X$1 BUT you said you have 1000's of cells to
format and the use of INDIRECT or OFFSET may slow things down.

It seems that using a helper column with a marker like Max suggested may be
your BEST option.

Biff

 

[Max]

Perhaps this could work ..

Use Edit > Replace on say col A to temporarily render the formulas into text.
Find: =
Replace with say: xx
Replace All

Then put in K1, and copy down:
=IF(RIGHT(A1,2)="/2",2,"")
This marks all the rows at one go

Kill the formulas in col K (copy > paste special as values "in-place"), then
restore col A's formulas with a "reverse" Edit > Replace, viz: Find: xx,
Replace with: =, Replace All. Then just apply the CF as per earlier.

As for:

.. another workbook with a similar situation
that has already maxed out the number of columns

Essentially the same method, but here we've to use a helper sheet and an
INDIRECT in the CF in the source sheet to get this up.

Assume the source sheetname is X. After edit > replace to temporarily
convert col A into text, in a new sheet named: Y, place in A1 and copy down:
=IF(RIGHT(x!A1,2)="/2",2,"")

Then kill the formulas in Y's col A and restore X's col A's formulas
(reverse the edit > replace). Then select the entire sheet X (with A1 active)
and apply the cond format formula as:
=INDIRECT("'Y'!A"&ROW(A1))=2

 

[PapaDos]

Why not creating a UDF that returns a cell formula and use it in your
conditional formatting conditions ?

 

[Sarah Jane]

I am not familiar with UDF. Can you give me some additional information
about it?
Thanks,
Sarah Jane

 

[PapaDos]

"User defined function", a custom macro that you can use as a worksheet
function.
In your case a simple one would do, like:

Function getCellFormula(r As Range)
getCellFormula = r.Formula
End Function

If you enter =getcellFormula(a1) in a cell, the cell will contain the
formula of the A1 cell...

So for your conditional formatting, try this formula:
=RIGHT(SUBSTITUTE(getCellFormula(B11), " ", ""), 2) = "/2"

 

[Sarah Jane]

THANK YOU! THANK YOU! THANK YOU!

I finally got back to that project to try your suggestion. It worked
perfectly!!!

Now I don't have to worry if I forgot to add or remove the "/2" in certain
cells because the cells with the "/2" are highlighted which makes it easy to
see if I got all the appropriate cells. Again, thank you so much.

Sarah Jane

 

[PapaDos]

Bienvenue !
:-]