Change Cell Font Color if Cell Contains Formula Referencing Another Cell

[wilcox.patrick]

Hello, I've learned tons from these forums over the years but not found the answer to this particular question.

I have a spreadsheet which is filled with numeric simple data (Cell XX="10"), numeric formula data (Cell YY="=5+6"), and cellular reference numeric data (Cell ZZ="=XX*5+2").

In order to quickly differntiate which numbers are hard vs. those that refer to other entries (XX and YY differently than ZZ), I use a font color convention. Hard entries (XX, YY) are blue font color. Dependent entries (ZZ) are black font color.

I have these entries through multiple sheets in a workbook and want this font color scheme to persist throughout the needed sheets in the workbook (not all but most with specific names) without having to run a macro but rather having it run in the background continuously on those sheets.

I found many ways of programming font colors based on different triggers but cannot find this particular scenario. I have basic competency in VBA at best.

Please point me in the direction of code to create this scheme without manual font choice selection.

Thanks in advance.
Patrick

[jeffreybrown]

Hi Patrick,

At least for me, it would help to see a sample workbook.

[wilcox.patrick]

Certainly Jeff, my description is a bit confusing.

I've attached a very simplified version of the workbook. The majority of items occur across the Trinkets and Widgets tabs as far as quantity and unit cost referencing each other or being hard entered.

There are usually 300-500 lines and the quantity may change depending on which department uses the spreadhseet (some departments use code only and have a blue quantity column and black quantity column, then two total columns as well).

Either way is fine by me, I just want to see blue hard entered data and black referenced data.

Hope this helps!

Thanks,
Patrick
Simplified Sample Workbook.xlsx

[jeffreybrown]

Hi Patrick,

Quite honestly, can't seem to come up with a way to do this without a macro, but I would go with a macro...

Please Login or Register  to view this content.

Then in conditional formatting:

Conditional Formatting

• Highlight applicable range >> E4:E9
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =Myformula(E4)
• Format… [Number, Font, Border, Fill]
• OK >> OK

Note: Or highlight the range that works for you.