Cell Formatting Based on Data Source

[hprice1128]

I would like to be able to run a macro which automatically formats cells based on their data source. The criteria would be 1) if a cell is hard coded, color blue 2) if a cell contains a formula, color black, 3) if a cell contains a link to another worksheet or workbook, color green.

Let me know if you have any ideas!

 

[JoeMo]

Does this do what you want?

Sub ColorCells()
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    Select Case True
     Case c.Hyperlinks.Count > 0: c.Interior.Color = vbGreen
     Case c.HasFormula: c.Interior.Color = vbbalck
     Case Else
        If Not IsEmpty(c) Then c.Interior.Color = vbBlue
    End Select
Next c
Application.ScreenUpdating = True
End Sub

If you prefer to apply this only to cells or a range you select, change "ActiveSheet.UsedRange" to "Selection" (w/o the quote marks).

 

[hprice1128]

Thanks Joe - Does "Hyperlinks.Count" only count links to external data sources? I am trying to capture links to other sheets within the same workbook not external sources. For example; if data on Tab 1 is coming directly from Tab 2 that data should be green.

 

[JoeMo]

Hmm, seems your choice of wording in item 3) of your OP may be suspect. Maybe you did not mean hyperlinks when you used the term "links". Can you elaborate on what a "link" is? Is it a formula that references another sheet? If yes, then it will be filled "black" per your rule 2). If you meant hyperlink then the code I provided will capture cells with hyperlinks to other sheets in the workbook and fill them green.

 

[hprice1128]

Like you described, when I said “links” I meant formulas which reference other work sheets. Is there a way to format those types of formulas as green?

 

[JoeMo]

Like you described, when I said “links” I meant formulas which reference other work sheets. Is there a way to format those types of formulas as green?

Possibly. How many other sheets are involved? Can you post an example of a formula for each of those sheets?

 

[hprice1128]

Possibly. How many other sheets are involved? Can you post an example of a formula for each of those sheets?

Example: "= 'Tab 2'!A1"

- This formula would be on a "Tab 1" and pull data directly from "Tab 2" without modifying it. -

- There would be an undefined number of sheets. The formula should be dynamic enough to capture formulas pulled from new sheets.

- My thought was to use something similar to your Hyperlinks.Count but instead count the ( ' ) character because I believe that is what is used to identify other sheets in excel.

If you have an email I could send you a file with a simple example. Thank you!

 

[JoeMo]

See if this works for you:

Sub ColorCells()
Dim c As Variant
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Interior.Color = vbBlue
On Error GoTo 0
On Error Resume Next
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If Err.Number <> 0 Then Exit Sub 'No formulas on active sheet
        If InStr(c.Formula, "!") > 0 And Not InStr(c.Formula, ActiveSheet.Name) > 0 Then
            c.Interior.Color = vbGreen
        Else
            c.Interior.Color = vbBlack
        End If
        On Error GoTo 0
Next c
Application.ScreenUpdating = True
End Sub