Format cell color based on condition

[Tkeller]

I am interested in VBA code that could do the following two things to my multi worksheet workbook:


PART 1. color code cells that are used as "imports"

I would like to go through each worksheet, cell by cell, and in any cell where there is a reference to another worksheet, I would like to change the font color to blue. for example, if I am on a worksheet named "outputs" and cell B1 contians the formula =Inputs!E25, then the font for this cell should be blue. The macro should be able to change the interior color to blue

PART 2. color code cells that are used as "exports".

This is probably a bit trickier, but here what I would like to do is to go through each worksheet, cell by cell, and in any cell where the value is used as an input on another worksheet (the reverse of the above PART 1), I would like to change the font color to red. So, using the example above on the input worksheet, cell E25 should be colored red.

I realize this may be difficult to do, particularly PART 2. Any help would be greatly appreciated!

Thanks.

 

[Smitty]

For the first you can use SpecialCells:

Sheets("Sheet1").Cells.SpecialCells(xlCellTypeFormulas, 23).Font.Color = vbBlue

For the second, I'm not too sure, but you might want to look into the ShowDependents method in the VBA helpfile.

HTH,

 

[Tkeller]

Thank you but I am not an expert in VBA. How would I put this into a macro that would go through each worksheet and cell one by one and make the change.

thanks so much.

 

[Smitty]

You can use a For Each...Next construct:

Sub foo()
  Dim ws as Worksheet
    For Each ws in ActiveWorkbook.Worksheets
      ws.Cells.SpecialCells(xlCellTypeFormulas, 23).Font.Color = vbBlue
    Next ws
ENd SUb

 

[mikerickson]

I think it might be easier to use the Trace Precedent and Trace Dependent tools that come with Excel.

What is the purpose of this cell coloring?

 

[Tkeller]

Thank you, but this ends up turning almost every cell in my worksheet blue! What I need to do is only turn cells blue that reference ANOTHER WORKSEET. Perhaps by searching for the "!" character?

 

[Tkeller]

I am familiar with trace precedent and dependent, but I have a multi worksheet model and I need to now go and change every single cell that either 1) has a refernce to another worksheet or 2) if used in another worksheet. I need something automated, not manual.

I need to do this as my company has a standard that all models must have these cells colored as such for eas of auditing, sharing, etc.

I can do this manually, but would take forever

Thanks!

 

[mikerickson]

If you explain TracePrecedents and TraceDependents to your company, they will change that fool policy.

 

[Smitty]

they will change that fool policy.

No doubt a policy put in place by some SOX auditor who doesn't know the first **** thing about Excel.

 

[Tkeller]

If you explain TracePrecedents and TraceDependents to your company, they will change that fool policy.

You are right - it's a ridiculous policy. But, despite my strong objections, and demoing the trace dependents/precedents feature, I can not win this fight. As I am stuck with it, would really appreciate some help so I don't have to waste any more time with it.

Thanks