Conditional Formatting Is Triggereing When and Where I DOn't Want It To

[Ark68]

Consider this code:

Sub ws_prepare()

    Dim wshcore As Worksheet
    Dim wshvar As Worksheet
    Dim wshwo As Worksheet
    Dim wshfac As Range
    Dim wshstaff As Worksheet
    Dim arr, g
    Dim i As Integer
    
    Dim cnta As Integer
    Dim llastrow As Integer
    Dim cnt_rec As Integer
    Dim cnt_rowsin As Integer
    Dim rngRIDCopy As Range
    Dim rngcore As Range
    Dim rptval As String
    
    Set wshwo = Worksheets("MasterWKSH")
    Set wshcore = Worksheets("CONTROL_1")
    Set wshvar = Worksheets("varhold")
    Set wshstaff = Worksheets("Staff")
    Set wshfac = Worksheets("Facilities").Range("A1:G300")
    
    cnt_rec = Application.Count(wshcore.Range("A:A"))
    cnt_rowsin = cnt_rec
    Set rngRIDCopy = wshcore.Range("A2:A" & cnt_rec + 1)
    Set rngcore = wshcore.Range("A:EH")
    
    With wshwo
    
        If .FilterMode Then .ShowAllData

        cnta = Application.Count(.Range("A:A"))
        If cnta > 0 Then
            .Rows("13:" & cnta + 12).Delete
        End If
        
        .Rows("13:" & cnt_rec + 12).Insert Shift:=xlDown
        With rngRIDCopy
            .Copy
        End With
        .Range("A13").PasteSpecial Paste:=xlPasteValues
        For i = 13 To cnt_rec + 12
            .Range("C" & i) = Application.VLookup(.Range("A" & i), rngcore, 3, False)
            .Range("D" & i) = Application.VLookup(Application.VLookup(.Range("A" & i), rngcore, 10, False), wshfac, 7, False) ' Location
            .Range("E" & i) = Application.VLookup(.Range("A" & i), rngcore, 6, False)
            .Range("F" & i) = Format(Application.VLookup(.Range("A" & i), rngcore, 14, False), "h:mm A/P")
            .Range("G" & i) = Format(Application.VLookup(.Range("A" & i), rngcore, 15, False), "h:mm A/P")
            .Range("H" & i) = Application.VLookup(.Range("A" & i), rngcore, 24, False)
            .Range("I" & i) = Application.VLookup(.Range("A" & i), rngcore, 31, False)
            .Range("J" & i) = Application.VLookup(.Range("A" & i), rngcore, 52, False)
            .Range("K" & i) = Application.VLookup(.Range("A" & i), rngcore, 55, False)
            .Range("L" & i) = Application.VLookup(.Range("A" & i), rngcore, 58, False)
            .Range("M" & i) = Application.VLookup(.Range("A" & i), rngcore, 71, False)
            .Range("N" & i) = Application.VLookup(.Range("A" & i), rngcore, 79, False)
            .Range("O" & i) = Application.VLookup(.Range("A" & i), rngcore, 87, False)
            .Range("P" & i) = Application.VLookup(.Range("A" & i), rngcore, 95, False)
            .Range("Q" & i) = Application.VLookup(.Range("A" & i), rngcore, 63, False)
            .Range("R" & i) = Application.VLookup(.Range("A" & i), rngcore, 5, False)
        Next i
        
        '** SORT **
        '.Range("A13:R" & cnt_rec + 12).Sort key1:=Range("R13"), order1:=xlAscending, key2:=Range("F13"), order2:=xlAscending, Header:=xlNo
        
        Dim oRangeSort As Range
        Dim oRangeKey As Range

        ' one range that includes all colums do sort
        Set oRangeSort = .Range("A13:R" & cnt_rec + 12)
        ' start of column with keys to sort
        Set oRangeKey = .Range("R13")

        ' custom sort order
        Dim sCustomList(1 To 6) As String
        sCustomList(1) = "DT"
        sCustomList(2) = "DR"
        sCustomList(3) = "FT"
        sCustomList(4) = "FR"
        sCustomList(5) = "CT"
        sCustomList(6) = "CR"
        
        Application.AddCustomList ListArray:=sCustomList
        ' use this if you want a list on the spreadsheet to sort by
        ' Application.AddCustomList ListArray:=Range("D1:D3")

        .Sort.SortFields.Clear
        oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, key2:=Range("F13"), order2:=xlAscending, Header:=xlNo, _
            OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        
        '** MISC **
        
        .Range("O4") = "MSTR"
        .Range("M4") = "MIN Start"
        .Range("P4") = "MAX End"
        .Range("M5") = Application.Min(.Range("F:F"))
        .Range("P5") = Application.Max(.Range("G:G"))
    
        '** INSERT SEPARATOR ROWS
    
        'Dim r As Long, mcol As String, h As Long
        'r = Cells(Rows.Count, "R").End(xlUp).Row
        'mcol = Cells(r, 18).Value
        'For h = r To 2 Step -1
        '    If Cells(h, 18).Value <> mcol Then
        '        mcol = Cells(h, 18).Value
        '        Rows(h + 1).Insert
        '    End If
        'Next h

        '** PREPARE INDIVIDUAL WORKSHEETS **
        
        Worksheets.Add(After:=Worksheets(13)).Name = "WPL"
        Worksheets.Add(After:=Worksheets(13)).Name = "WPE"
        Worksheets.Add(After:=Worksheets(13)).Name = "RPL"
        Worksheets.Add(After:=Worksheets(13)).Name = "RPE"
        Worksheets.Add(After:=Worksheets(13)).Name = "HPL"
        Worksheets.Add(After:=Worksheets(13)).Name = "HPE"
        Worksheets.Add(After:=Worksheets(13)).Name = "CUL"
        Worksheets.Add(After:=Worksheets(13)).Name = "CUE"
        
        '** POPULATE WORKSHEETS
        
        .Range("H12") = "Groom"
        .Range("I12") = "Prepare"
        .Range("J12") = "Signature"
        .Range("K12") = "Lights On"
        .Range("L12") = "Lights Off"
        .Range("M12") = "1"
        .Range("N12") = "2"
        .Range("O12") = "3"
        .Range("P12") = "4"
        .Range("Q12") = "Close"
        
        '** START OF BUILD **
                
        arr = Array("CUE", "CUL", "HPE", "HPL", "RPE", "RPL", "WPE", "WPL")
        
        For g = 0 To UBound(arr)
            
            rptval = arr(g)
            
                
            With Sheets(arr(g))
                .Activate
                               
                With wshwo
                    If .FilterMode Then .ShowAllData
                    llastrow = .Range("R" & Rows.Count).End(xlUp).Row
                    wshvar.Range("I27") = Application.VLookup(rptval & 1, Worksheets("Staff").Range("A4:B19"), 2, False)
                    MsgBox "Report: " & rptval & Chr(13) & "Staff: " & wshvar.Range("I27")
                    
                    With .Range("A12:R" & llastrow)
                        .AdvancedFilter _
                            Action:=xlFilterInPlace, _
                            CriteriaRange:=wshvar.Range("I28:R38"), _
                            Unique:=False
                        On Error Resume Next
                    End With
                    Worksheets("MasterWKSH").Range("A1:R300").Copy
                End With
                
                With .Range("A1")
                    .PasteSpecial xlPasteColumnWidths
                    .PasteSpecial xlPasteAll
                End With
                    
                llastrow = .Range("R" & Rows.Count).End(xlUp).Row
                If llastrow < 2 Then
                    .Rows("13:34").Insert Shift:=xlDown
                    llastrow = 34
                End If
                
                With .Rows("1:300")
                    .RowHeight = 12.75
                    .VerticalAlignment = xlCenter
                End With
                    
                .Rows(7).RowHeight = 9.75
                .Rows(11).RowHeight = 6
                .Rows(llastrow + 3).RowHeight = 6.75
                .Rows(llastrow + 5).RowHeight = 6.75
                wshwo.Shapes("Picture 3").Copy
                .Range("A1").PasteSpecial
            
                .Range("M1") = Format(wshcore.Range("B2"), "dddd, mmmm dd, yyyy")
                .Range("M4") = wshvar.Range("I27")
                .Range("O4") = Application.VLookup(.Range("M4"), wshstaff.Range("L4:M20"), 2, False)
                .Range("P4") = Application.VLookup("RPL1", wshcore.Range("BA:DJ"), 58, False)
                .Range("M5") = Format(Application.VLookup("RPL1", wshcore.Range("BA:DJ"), 61, False), "h:mmA/P") & " - " & Format(Application.VLookup("RPL1", wshcore.Range("BA:DJ"), 62, False), "h:mmA/P")
                .Range("P5") = Format(Application.VLookup("RPL1", wshcore.Range("BA:DJ"), 59, False), "h:mmA/P") & "-" & Format(Application.VLookup("RPL1", wshcore.Range("BA:DJ"), 60, False), "h:mmA/P")
          
                With .Range("H13:Q" & llastrow)
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
                        Formula1:="=varhold!$I$27"
                    .FormatConditions(.FormatConditions.Count).SetFirstPriority
                
                    With .FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorLight1
                        .TintAndShade = 0.499984740745262
                    End With
                    With .FormatConditions(1).Font
                        .ThemeColor = xlThemeColorLight1
                        .TintAndShade = 0.499984740745262
                    End With
                    .FormatConditions(1).StopIfTrue = False
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                        Formula1:="=varhold!$I$27"
                    .FormatConditions(.FormatConditions.Count).SetFirstPriority
                    With .FormatConditions(1).Font
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                    End With
                    .FormatConditions(1).StopIfTrue = False
                End With
            End With
            
        Next g
    End With
End Sub

The portion highlighted in blue applies conditional formatting to the range of the currently active worksheet. Basically, cells not holding the value found in varhold.range("I27") have their backgrounds shaded and the font color changed to obscure the contents.

This works wonderfully, until the loop cycles to the next workbook. At this time, the value for varhold.range("I27") changes to reflect new criteria for the new worksheet.

I've discovered, that although no longer active, the previous worksheet having gone through the loop, gets changed. The conditional formatting is picking up the new value of varhold.range("I27") .
I was not aware that the change of I27 reflected on another worksheet would carry over into worksheets already processed.

I hope I make sense, if not, I will try to elaborate more clearly. This is a major hurdle to the the momentum of my application, and I hope the issue is easily taken care of.

Thanks in advance,

Jenn

 

[AD_Taylor]

Let me see if I've got this right,

You've applied some conditional formatting, Cell Value is not equal to varhold!$I$27.
The value in varhold!$I$27 changes each time the loop goes through (I'm assuming this is the loop using g).
The conditional formatting applied to previous sheets changes to match whatever the last sheet sets it to.

If this is all correct then that's exactly what you've asked the code to do. Instead of extracting the value from varhold!$I$27 you've simply referenced it, meaning that when that value changes the conditional formatting will change as well.
Try changing the line that adds your formatting to this (change in red):

With .Range("H13:Q" & llastrow)                   
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:= [COLOR=#ff0000]Sheets("varhold").Range("I27").Value[/COLOR]
End With

 

[Ark68]

Hi AD_Taylor ... I appreciate your reply. It is complicated to try to explain, I was hoping that providing the entire sub would help. I tried your suggestion, and although no errors, and no repeat of the original issue, there was no conditional formatting performed when it should have. The original code did that, but got messedu p as the code moved from one worksheet to the other.

So ... let's see if I can use some quasi code to explain how it works ...

There are 8 reports (CUE, CUL, HPE, HPL, RPE, RPL, WPE, and WPL) . Each report is on a separate worksheet.

In the sub I posted ... all the code from the start to " '** Start of build **' is simply creating a master worksheet (wshwo). (adds rows, populates worksheets by extracting data from the working database and sorts it according to a custom sort). The master worksheet (MasterWKSH) holds all the records that will eventually be filtered to provide data to each of the individual reports. It also inserts the 8 respective blank worksheets into the workbook. The master workbook, columns H through Q, are populated with people's names as extracted from the . There is no conditional formatting done at this level. At this point, code to create the individual reports (worksheets) is initiated.

arr = Array("CUE", "CUL", "HPE", "HPL", "RPE", "RPL", "WPE", "WPL")
        
        For g = 0 To UBound(arr) ...
        Next g

This loops the report creation code between the 8 different reports. The same code is used to generate the individual reports, the variable being arr(g) and rptval, which, rptval = arr(g).


1. With wshwo (master database)
2. unfilter wshwo if in filtermode
3. count the number of records in the master worksheet (52) - llastrow
4. assign worksheet varhold (wshvar), cell I27 with the individual's name associated (through a vlookup) to the report amm(i) {CUE} = "Kelly"
5. filter the rows in wshwo to display only those rows in which "Kelly" exists in any of the cells in the range A12:R & llastrow. At this point, the master worksheet will have Kelly (among other names) in at least one cell per row.

6. the filtered master worksheet is copied (10 rows)
7. its contents, column width's pasted in the destination worksheet amm(i) = CUE
8. some row height adjustments are made
9. some static cells are populated

10. conditional formatting is applied. Any values in the cells H13:Q & llastrow not equal to "Kelly" (wshvar.I27) results in those cells being shaded, and the font coloured. The values (names) remain in the cells, but are obscured. Those cells containing "Kelly" are not shaded, but the font is turned white, thus giving the appearance the cell is empty.
11. CUE report is now complete. All cells with names other than Kelly are obscured.

12. next g = amm(2) = CUL

The code recycles now to step 1. At step 4, wshvar.I27 is assigned the new value of "Mike" as determined from the vlookup report CUL.

It is at this point the problem surfaces!
The cells of the previous report formatted conditionally to "exclude" any values other than "Kelly", now become influenced by the new name "Mike". So, all the references in CUE of mike, are now visible, and references to kelly, are obscured ...

The conditional formatting for CUE must not change when the name changes between different reports. It must retain it's conditional formatting based on it's initial I27 = Kelly.
In CUL, conditional formatting must be based on I27 = Mike.
To continue, amm(3) - HPE , I27 will change to Kate, and conditional formatting on that sheet will be based on "Kate". But, at this point in the loop ... CUE and CUL conditional format results change to reflect Kate, when it should stay Kelly and Mike respectively.

Anyone who would like to see it for themselves ...
https://docs.google.com/file/d/0B9EE-tbOy4bJLVh0ZEZGYXhWTmM/edit?usp=sharing

I'd be very grateful to anyone that can assist in a solution.

 

[AD_Taylor]

I think I understood most of that from the first post but I don't see why my code would stop the conditional formatting working. I did only give you the part that you needed to change so maybe showing it like this will work?

This should replace the entire bit highlighted blue in your first post.

                With .Range("H13:Q" & llastrow)
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
                        Formula1:= wshvar.Range("I27").Value
                    .FormatConditions(.FormatConditions.Count).SetFirstPriority
                
                    With .FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorLight1
                        .TintAndShade = 0.499984740745262
                    End With
                    With .FormatConditions(1).Font
                        .ThemeColor = xlThemeColorLight1
                        .TintAndShade = 0.499984740745262
                    End With
                    .FormatConditions(1).StopIfTrue = False
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                        Formula1:= wshvar.Range("I27").Value
                    .FormatConditions(.FormatConditions.Count).SetFirstPriority
                    With .FormatConditions(1).Font
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                    End With
                    .FormatConditions(1).StopIfTrue = False
                End With