This project has moved. For the latest updates, please go here.

Conditional formating of a named range

Feb 16, 2016 at 2:40 PM
Need to apply conditional formatting to a named range or other range in which the cells or columns in the range are not contiguous.

Below a named range is built from each column in which the value includes the word "DoDAAC." These columns are not necessarily beside one another and I need to know if there are duplicate values in them. I am attempting to do that by creating a conditional format rule that will highlight dups throughout the columns that are not beside one another. Now I know I can do this several ways at creation of the sheet by collecting the values in a list, looking for dups and highlighting them. But the resulting spreadsheet is distributed and updated by users after creation. If they enter a duplicate value I want the cell to turn red.

If the below range "rg" is a contiguous range, no problem the conditional formatting works. However, if there are many ranges in "rg" like "I:I,K:K,M:M" which can be the case in a named range then only the first range has the formatting applied.

'this will work fine if the range "rg" is equal to a normal contiguous range

But if I create a non-contiguous range, like below, the formatting only gets applied to the 1st range in the list of ranges included in the named range.

'Create a named range in the worksheet with all the DoDAAC columns
    For Each C As cxl.IXLCell In .Row(1).CellsUsed
        If C.Value.ToString Like "*DoDAAC*" Or _
           C.Value.ToString Like "*DoDAAC*" Or _
           C.Value.ToString Like "*DoDAAC*" Then
            C.WorksheetColumn.AddToNamed("DoDAACs", ClosedXML.Excel.XLScope.Workbook)
        End If
'make a range object equl to the named range in the worksheet
Dim nr = wkb.Range("DoDAACs")

'Here I attempt to apply the formatting to the named range and it only get applied to the first range in the ranges of the named range.

It wont work to cycle thru all the ranges and apply the formatting separately as I'm checking for duplicate across all the columns.