How to overlap the changes made in VBA

Question

Below us the code that I want some clarification for:

'Auto format the cells when you change cell B39

If Not Intersect(Target, Range("B39")) Is Nothing Then

    If InStr(1, Range("B39"), "ABC") > 0 Then
        Range("B13:B18,B22,B23,B25").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = RGB(100, 250, 150)
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("B19:B21,B24,B26:B35").Select
        With Selection.Interior
                .Pattern = xlNone
                .PatternTintAndShade = 0
        End With
    Else: Range("B13:B35").Select
        With Selection.Interior
                .Pattern = xlNone
                .PatternTintAndShade = 0
        End With
    End If
End If

If Not Intersect(Target, Range("B57")) Is Nothing Then

    If Range("B57") = "DEF" Then
        Range("B13:B18,B22,B23,B25,B30,B35").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = RGB(100, 250, 150)
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("B19:B21,B24,B26,B27:B29,B31:B34").Select
        With Selection.Interior
                .Pattern = xlNone
                .PatternTintAndShade = 0
        End With
    End If
End If
End Sub

In cell B39 and B57 I have drop down list of products produced by the company. The above code highlights the requirements to manufacture those products. When I select a SKU from B39 the code highlights the range specified. Same for B57. When I change B39 first and then change B57 the highlighted cells change, I dont want this to happen. I want the changes due to selecting a SKU from B39 to remain even after changing B57.

Hope this clarification is better.

Thank you!

Please explain better what you are trying to do with examples. "B39 is currently 'foo', if I change it to 'bar' I want... blah blah blah". I get the definition of "Fix" you are after here, but how it applies to your data/code isn't clear. "Overlap the changes" isn't clear either. It would help if you could explain, without using made up technical terms, what you are after. — JNevill, Oct 15 '18 at 16:34
(It's best [not to use `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)) — BruceWayne, Oct 15 '18 at 17:17
@BruceWayne Even if I wont use Select how will it make my code not overlap the selections? — LewisThelemon, Oct 15 '18 at 18:01
When you're doing the selection, can you guarantee `B39` will be changed and **immediately** after, `B57` will be changed? Or, could the user change B39, then do things, then change `B57` where you **don't** want the highlighted cells to change? — BruceWayne, Oct 15 '18 at 18:19
@BruceWayne I want the user to change B39, can do some stuff and then change B57 where I dont want the highlighted cells to change. — LewisThelemon, Oct 15 '18 at 18:34
At what point can they change `B57` where you *want* the selection change? Or is it, if they open the file, change `B39` **at all, ever** during the file opening, then if they select `B57` you don't want the change? — BruceWayne, Oct 15 '18 at 18:35
@BruceWayne Okay let me clarify more: B39 is a drop down list with products running on say line 1 B57 is a drop down list with products running on line 2 The products in the list for B39 are different from products in B57 list. They both require some raw materials which are common for some and different from other. Those materials are listed in Range("B13:B35"). When I select a product from B39 it highlights the raw materials required for that product and same for when I select one from the B57 list. — LewisThelemon, Oct 15 '18 at 18:41
Wait, so are you just wanting to visibly highlight cells based on what's chosen in the drop down(s)? Why not use conditional formatting? — BruceWayne, Oct 15 '18 at 18:44
@BruceWayne So the selection of the products is independent and a user may or may not change the products in the lists. So say I select a product from B39 list 'ABC' and another from B57 list. If I change the product from B57 list it should not change the highlighted cells for B39. — LewisThelemon, Oct 15 '18 at 18:46
@BruceWayne I am not using conditional formatting because there are a lot of SKUs and it slows the excel down. Plus there are other complications. — LewisThelemon, Oct 15 '18 at 18:48

score 0 · Accepted Answer · answered Oct 15 '18 at 19:25

Seems like the problem is that you have overlapping ranges you want to hilite/clear: you can't manage the highlighting like you're trying to when you have products with overlapping materials. What you'd need to do is clear all highlighting, then check each "target" cell to see what hilighting needs to be re-added: don't just check the one Target cell which was changed.

I moved the hiliting into a separate sub and removed any Select steps - these are typically best avoided.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B39,B57")) Is Nothing Then

        Hilite Range("B13:B100"), False '<< clear *all* hiliting

        'add back any required hilites
        If InStr(1, Range("B39"), "ABC") > 0 Then
            Hilite Range("B13:B18,B22,B23,B25"), True
        End If
        If Range("B57") = "DEF" Then
            Hilite Range("B13:B18,B22,B23,B25,B30,B35"), True
        End If

    End If
End Sub

'add/remove hilighting on a supplied range
Sub Hilite(rng As Range, hilight As Boolean)
    With rng.Interior
        If hilight Then
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = RGB(100, 250, 150)
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Else
            .Pattern = xlNone
            .PatternTintAndShade = 0
        End If
    End With
End Sub

Thanks a lot! This Works! and you gave me an idea to better my code too! Thanks! — LewisThelemon, Oct 16 '18 at 15:49
If its not much trouble for you, could you please give me a description of what does the IF Not line is doing and why would we use a Boolean for the function. I would like to understand this better. Thanks — LewisThelemon, Oct 17 '18 at 17:40
The If Not line checks to see if Target contains either of B39 or B57 - Intersect returns `Nothing` when there are no common cells in the two ranges. The boolean in the function controls whether to apply the hiliting (True) or clear it (False) — Tim Williams, Oct 17 '18 at 17:54

How to overlap the changes made in VBA

1 Answers1