Conditional formatting for a SPECIFIC formula or reference to another tab?

[HKFargo]

Conditional formatting for a SPECIFIC formula or reference to another tab?
Hi,

I'm tearing my hair over this. I'm trying to have 2 conditional formatting rules to highlight cells on these 2 things:

1) Any cell that contains a reference to another tab (the tab name is "Current FOC").

and

2) Any cell that contains an Offset formula

I'm doing Conditional Formatting -> Highlight Cell Rules -> Text that contains

This doesn't seem to work.


I have attached 2 images:

Column I contains cells with this formula as an example: ='Current FOC'!Z4

Column J contains cells with this formula as an example: =OFFSET(INDIRECT($A$30),0,0)

The 2nd image shows my conditional formatting for "text contains"


I have Microsoft 365 for Enterprise Version 2302 (if that helps). Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Select New Rule, use a formula & use

=ISNUMBER(SEARCH("Current FOC",FORMULATEXT(A1)))

 

[ExcelPowerN]

Format your cells based on "Use formula to determine which cells to format" and below formula for OFFSET

=IF(IFERROR(FIND("OFFSET",FORMULATEXT(C5)),0)>0,TRUE,FALSE)

Replace 'C5' with your first formatting cell and should be relative. Remove $ signs

similar formula for other condition

 

[Fluff]

Or use the simpler formula I suggested.

 

[HKFargo]

Hi & welcome to MrExcel.
Select New Rule, use a formula & use

=ISNUMBER(SEARCH("Current FOC",FORMULATEXT(A1)))

Thanks guys, i used this one and it worked!

 

[Fluff]

Glad we could help & thanks for the feedback.