conditional formatting based on text string in cell formula

Question

I inherited a workbook where the original owner did a lot of hand-edited formulas. I'd like to use conditional formatting to visually determine which cells are based on what source worksheets, e.g. all the source cells contain a vlookup, but the source data sheet differs across cells:

=vlookup(A1,Sheet1!A:Z,3,False)

=vlookup(A2,Sheet4!A:Z,15,False)

=Vlookup(A3,Sheet3!A:Z,5,False)

So, I'd like to color cells that contain "Sheet1" as part of the formula text blue, "Sheet3" as red, etc.

So far I haven't found a way to point the conditional formatting at the actual formula text. Any help would be appreciated, otherwise I'm stuck with creating a UDF to extract the text.

Thanks!

Answer 1

Select cell A1.

Activate the Formulas tab of the ribbon.

Click Name Manager.

Click New..

Enter GetFormula in the Name box.

Enter the formula  =GET.CELL(6,!A1)   (yes, exactly like that) in the Refers to box.

Click OK, then Close.

Select the cells that you want to format conditionally.

Activate the Home tab of the ribbon.

Select Conditional Formatting > Manage Rules...

Click New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula   =ISNUMBER(SEARCH("Sheet1",GetFormula))

Click Format...

Activate the Fill tab and select a suitable highlight color, e.g. pale blue.

Click OK twice.

Repeat the above series of steps from 'Click New Rule...', but with "Sheet3" and pale red, etc.

When you have defined all the rules you want, click OK to close the Conditional Formatting Rules Manager.

Answer 2

Hans, that is a great idea- I presented a simplified example for the sake of clarity, each of the sheet references are to named sheets (Bulk_Orders_Only, MB_Orders, etc), but I should be able to adapt your approach to grab text and set it up from there- thanks for the lead!