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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top