-2

I am trying to create a VBA script that will conditionally format a range of cells that contain any text string with a fill color of my choosing.

So far, I use an Excel conditional formatting rule to achieve this, and it works; however, dragging and dropping the contents of cells from one column to another causes the conditional formatting rules to become very fragmented and is quickly becoming a mess. What started out as two conditional formatting rules, one for column A and another for column B, rapidly becomes dozens of separate rules as Excel alters the "applies to" field of the rules with every copy or move of cell data.

enter image description here

A VBA script that is able to achieve the same thing as my conditional formatting rules would be much better as it would not be affected by moving or copying and pasting cell data. I would be able to freely drag and drop my data into the appropriate column without the underlying VBA code being affected.

Does anyone here with some basic VBA coding experience have any ideas for a simple piece of code I could use to simply change the fill color of any cells that contain any string? It would apply to cells A1:A200.

If you don't like something about my question for some reason, as David Postill did recently, please tell me in a comment and give me a few minutes to update it with whatever additional information you think may be necessary, instead of downvoting it and scurrying away.

Only interested in hearing from people with some basic VBA experience and a desire to be helpful. No snarky comments about "We are not going to debug some random script you found online for you" please. I only want to hear from positive, HELPFUL people.

Improve this question
10
  • 2
    Obtain 2-3 conditional formatting fragments. Start macro recording. Remove fragmented conditional formatting. Stop recording and start another. Add new, solid formatting. Stop. Investigate created macros' codes.
    – Akina
    Commented Jul 24, 2019 at 13:55
  • Hmm. I understand why the fragmentation is happening. What would doing this tell me exactly?
    – Mr Ethernet
    Commented Jul 24, 2019 at 13:57
  • 1
    You than can create your own macro attached to worksheet_change which will reset conditional formatting to solid state.
    – Akina
    Commented Jul 24, 2019 at 13:59
  • I'm sure that would work but it sounds like a bit of a workaround. It would essentially keep fixing the fragmented conditional formatting rules every time they broke. I would rather use a VBA script to apply conditional formatting so that there are no fragmented conditional formatting rules to fix in the first place—even if it is functionally identical. Does that make sense? I don't even think this method would necessarily be easier, so I can't think of any benefits to doing it this way. Or am I missing something? Just trying to use the most elegant solution really.
    – Mr Ethernet
    Commented Jul 24, 2019 at 14:03
  • 1
    I would rather use a VBA script to apply conditional formatting so that there are no fragmented conditional formatting rules to fix in the first place—even if it is functionally identical. Does that make sense? No. CF rules may overlap - and all of them will be evaluated. But you can consolidate not for each change, but only when the rules amount is too high (or on demand - instead or additionally).
    – Akina
    Commented Jul 24, 2019 at 16:42

2 Answers 2

Reset to default
3

It's annoying that conditional formatting can become fragmented, as you described. I try to write conditional formatting rules that apply to an entire column or columns. Then I can change a fragmented address like $B$24,$B$25:$C$25,$B$27:$C$1048576,$B$26,$B$21:$C$23,$B$1:$C$19,$B$20 back to $B:$C.

Since you reminded me about this annoyance, I wrote a macro to fix fragmented addresses in conditional formatting rules. The macro will help only if the conditional formatting rules apply to an entire column or columns. 

Sub ApplyConditionalFormattingToEntireColumns()
    Dim oneFormatCondition As FormatCondition
    Dim strAddresses() As String, lngA As Long
    Dim strFirst As String, strLast As String, strCheck As String

    For Each oneFormatCondition In ActiveSheet.Cells.FormatConditions
        strFirst = ""
        strLast = ""
        'Splits each condition's addresses into an array.
        strAddresses = Split(oneFormatCondition.AppliesTo.Address, ",")
        For lngA = LBound(strAddresses) To UBound(strAddresses)
            'Finds and saves the first column.
            strCheck = strAddresses(lngA)
            strCheck = Mid(strCheck, 2, _
                InStr(2, strCheck, "$", vbTextCompare) - 2)
            If strFirst = "" Then strFirst = strCheck
            If strLast = "" Then strLast = strCheck
            If strFirst > strCheck Then strFirst = strCheck
            If strLast < strCheck Then strLast = strCheck
            'Finds and saves the last column.
            strCheck = strAddresses(lngA)
            If InStr(2, strCheck, ":", vbTextCompare) > 0 Then
                strCheck = Right(strCheck, Len(strCheck) - _
                    InStr(2, strCheck, ":", vbTextCompare))
                strCheck = Mid(strCheck, 2, _
                    InStr(2, strCheck, "$", vbTextCompare) - 2)
                If strLast < strCheck Then strLast = strCheck
            End If
        Next lngA
        'Modifies each condition's address to entire columns.
        oneFormatCondition.ModifyAppliesToRange _
            Range("$" & strFirst & ":$" & strLast)
    Next oneFormatCondition
End Sub
Improve this answer
3
  • 1
    It took me a while to understand what was happening when my rules started changing themselves but it makes sense: Excel is trying to be helpful and allow us to move things around, dragging every property along with the text that we see, unless we explicitly tell it otherwise. Thanks for the code. I will try it out tonight. Do you know how hard it would be to try to replicate Excel conditional formatting with a macro?
    – Mr Ethernet
    Commented Jul 25, 2019 at 5:50
  • @wrecclesham Are you looking for something like Range("A1:A3").FormatConditions.AddColorScale(3)?
    – Mast
    Commented Aug 7, 2019 at 15:36
  • @Mast I got it figured out in the end. You can see the VBA I'm using now in my answer below. I'll give your code a whirl though and see if it could help me in the future. Thanks!
    – Mr Ethernet
    Commented Aug 7, 2019 at 19:45
0

The folks over at MrExcel.com were able to come up with a very elegant solution.

It turns out it was possible to replicate the functionality of my existing conditional formatting rules using just five lines of VBA code. The issue of rules being altered as the data is moved around can no longer happen as the conditional formatting logic is now handled by a small macro.

I have spent a few minutes testing this and it works well. I have now deleted all of my conditional formatting rules and the same conditional formatting behavior lives on through this VBA code:

With Range("A1:B200")
  .Interior.Color = xlNone
  .Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 22
  .Offset(, 1).Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 36
End With

For context, here is the entire VBA code I now use on this worksheet.

The first section handles automatic alphabetization while this new second section handles the conditional formatting:

Private Sub Worksheet_Change(ByVal Target As Range)

Range("A1:A200").Sort Key1:=Range("A1"), _
  Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom

      Range("B1:B200").Sort Key1:=Range("B1"), _
  Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom

With Range("A1:B200")
  .Interior.Color = xlNone
  .Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 22
  .Offset(, 1).Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 36
End With

End Sub
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .