Cell Formatting Based on Data Source

hprice1128

New Member
Joined
Nov 20, 2015
Messages
8
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!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,731
Does this do what you want?
Code:
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

New Member
Joined
Nov 20, 2015
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,731
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

New Member
Joined
Nov 20, 2015
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,731
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

New Member
Joined
Nov 20, 2015
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,731
See if this works for you:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,954
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top