VBA code using intersect to select cells and apply a conditional format to

Question

I am after a solution to get around the limitations whereby you cannot select (highlight) a column and row properly if it contains merged cells. Unfortunately I cannot unmerge the cells due to the loss of formatting; therefore I’m hoping to rather create a conditional formatting rule which only applies to the 'Intersect' cells. I can then use the conditional formatting in this sub and delete it in others as required.

Below is the working code used to select (highlight) the intersect column and row (that are unmerged). I am hoping that someone knows of a method to create a conditional formatting rule that will simply colour the matching interest cell backgrounds green once located. Any ideas would be great? :-)

Sub previewSub()
   Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call previewDisplayColumnRow(ws)

    Next
        MsgBox "Click on each Worksheets to preview columns/rows which will be displayed after clicking on 'UPDATE LAYOUT'"
End Sub

Sub previewDisplayColumnRow(ws As Worksheet)

    Dim DisplayColumns       As Variant
    Dim DisplayRows          As Variant
    Dim HideColumnsRows      As Variant
    Dim SelectColumnsRows    As String
    Dim myrange

    Set myrange = Worksheets("Settings").Range("range_sheetProperties")

    'Lookup Worksheet name and identify columns & rows to display
    HideColumnsRows = Application.VLookup(ws.Name, myrange, 6, False)

    If Not IsError(HideColumnsRows) Then 'If no error.

        DisplayColumns = Application.VLookup(ws.Name, myrange, 7, False)
        DisplayRows = Application.VLookup(ws.Name, myrange, 8, False)

        SelectColumnsRows = DisplayColumns & "," & DisplayRows

       'Selects Columns / Rows which will be hidden
        If HideColumnsRows = "Y" Then
            'MsgBox ws.Name & " - " & SelectColumnsRows
            ws.Activate
            Intersect(ws.Range(DisplayColumns), ws.Range(DisplayRows)).Select
        End If

    End If
End Sub

cRIMSOn ffs cRIMSOn ffs · Accepted Answer · 2014-12-09T07:15:28

for those that needed a hand in the future, I got this to work. See below!

Sub previewSub()
   Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call previewDisplayColumnRow(ws)

    Next
        Sheets("Settings").Activate
        MsgBox "Click on each Worksheets to preview columns/rows which will be displayed after clicking on 'UPDATE LAYOUT'"
End Sub

Sub previewDisplayColumnRow(ws As Worksheet)

    Dim DisplayColumns       As Variant
    Dim DisplayRows          As Variant
    Dim HideColumnsRows      As Variant
    Dim SelectColumnsRows    As String
    Dim myrange

    Set myrange = Worksheets("Settings").Range("range_sheetProperties")

    'Lookup Worksheet name and identify columns & rows to display
    HideColumnsRows = Application.VLookup(ws.Name, myrange, 6, False)

    If Not IsError(HideColumnsRows) Then 'If no error.

        DisplayColumns = Application.VLookup(ws.Name, myrange, 7, False)
        DisplayRows = Application.VLookup(ws.Name, myrange, 8, False)

        SelectColumnsRows = DisplayColumns & "," & DisplayRows

       'Selects Columns / Rows which will be hidden
        If HideColumnsRows = "Y" Then
            'MsgBox ws.Name & " - " & SelectColumnsRows
            ws.Activate
            Cells.FormatConditions.Delete
            ws.Columns.EntireColumn.Hidden = False
            ws.Columns.EntireRow.Hidden = False

            With Intersect(ws.Range(DisplayColumns), ws.Range(DisplayRows))
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$A$1<>""WHOs GOING TO TYPE THIS NOBODY"""
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 13561798
                .FormatConditions(1).Interior.TintAndShade = 0
            End With
        End If
    End If
End Sub

VBA code using intersect to select cells and apply a conditional format to

1 Answers