1

I am trying to develop a code that color-codes cells based on cell contents i.e. based on the following criteria

  1. Hardcoded cells
  2. Cells with formula
  3. Cells with links to other sheets
  4. Cells with external links

The issue in here is that the code I have been able to develop uses actual background colors to color code the cells.

I am looking for a way to use conditional formatting to run the same code so that I can develop a code to unshade the cells and it restores to original formatting in the sheet.

Can someone help me converting the code to use conditional format?

Improve this question

2 Answers 2

Reset to default
1

This answer relies on the FORMULATEXT function which is not available prior to Excel 2013. Also I'm going to crib some code from this useful post which shows a reasonably simple way of adding multiple conditions to cells.

So for your 4 criteria we can use FORMULATEXT for each:

  1. Hardcoded cells: =AND(ISNA(FORMULATEXT(A2)),NOT(ISBLANK(A2)))
  2. Cells with formula: =NOT(ISNA(FORMULATEXT(A2)))
  3. Cells with links to other sheets: =NOT(ISERROR(SEARCH(""*!*"",FORMULATEXT(A2),1)))
  4. Cells with external links: =NOT(ISERROR(SEARCH(""*.xls*]*!*"",FORMULATEXT(A2),1)))

Noting:

  • Formula 1 has the distinction that the logic says any value that is not a formula which most of the time will mean a hard-coded value.

  • Formula 2 is the inverse of Formula 1 in that any non-error with FORMULATEXT means it's a formula

  • Formulas 3 and 4 are per your wildcard logic except I only implemented the Like "*!*" bit and not the other clauses - you can extend the formula for this although it might end up on the complex side.

The order within the conditional format matters. In the code below, the order is actually 1, 4, 3, 2 because 4 is a special case of 3 (i.e. contains a !) and 4 and 3 are special cases of 2 (i.e. all 3 are formulas).

So the code I used is:

Option Explicit

Sub CellShader()

    Dim rng As Range
    
    Set rng = Sheet1.Range("A2:A6")
    
    ' delete existing conditional formatting
    rng.FormatConditions.Delete
    
    ' add your 4 rules in order 1, 4, 3, 2
    AddRule rng, "=AND(ISNA(FORMULATEXT(A2)),NOT(ISBLANK(A2)))", vbCyan
    AddRule rng, "=NOT(ISERROR(SEARCH(""*.xls*]*!*"",FORMULATEXT(A2),1)))", vbMagenta
    AddRule rng, "=NOT(ISERROR(SEARCH(""*!*"",FORMULATEXT(A2),1)))", vbRed
    AddRule rng, "=NOT(ISNA(FORMULATEXT(A2)))", vbGreen
   
End Sub

' https://stackoverflow.com/questions/40209398/conditional-formatting-vba-multiple-conditions
Sub AddRule(rng As Range, strFormula As String, lngColor As Long)
    With rng.FormatConditions
        With .Add(Type:=xlExpression, Formula1:=strFormula)
            .Interior.Color = lngColor
            .StopIfTrue = True
        End With
    End With
End Sub

Used on these test cases it produces the correct result:

enter image description here

You can see A2 is blank and is not being formatted as it is a not-a-formula, but does not contain any value (hard-coded or otherwise).

HTH

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi Robin, thanks for the code. It works for the most part. I had a quick question as to something where I found an issue and I think I have been able to fix it. So in the formula used, it appears that you have a reference to cell A2 and when I tried the code on my system, the conditional formatting got applied to row -1. Did the same happen for you too? I replaced the code to show A1 instead of A2 and it then worked fine. Not sure if this is what was meant to be. Apologies for the late reply!!
@nikhilkumar - I used A2 in the formulas because it's the first cell of the range that the conditional formatting is applied to per Set rng = Sheet1.Range("A2:A6"). So for your worksheets you would likely need to update A2 with whatever the first cell of the range is for your data. HTH
That makes sense. Thank you for the help. I will actually be making it dynamic by using Set rng = Application.selection and I think I will then have to use A1. Let me know if that makes sense? I will have to share the code with a few colleagues and wanted it to be dynamic and hence wanted to be sure if what I am thinking is correct. Thanks a lot again for help and quick responses
Yep maybe you can get the address of the first cell of the selection with Selection.Cells(1, 1).Address(False, False) and substitute that value into the formula. Don't think A1 would magically work, think you want to use the top left hand cell of the range
Makes sense, thank you for the quick feedback. I will try it today and will let you know
0

If you just want to reset the colours you could write a new sub to loop through the cells and set the interior colour back to white or (255, 255, 255)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.