conditional formatting varios named cells

The only way (that I know of), to apply CF without resorting to VBA, is to actually select the cell you need to apply the CF to (or, when you select a range)

I just tried to use a named range in CF and it did not work, so I think you are stuck with either doing each manually, or format copying, then adjusting

CF appears very flexible on the surface but whenever you try to do anything outside the obvious box it quickly becomes either impossible or so complicated that it is often not worth the effort

What condition are you applying to these cells?

Originally Posted by kev_

CF appears very flexible on the surface but whenever you try to do anything outside the obvious box it quickly becomes either impossible or so complicated that it is often not worth the effort

What condition are you applying to these cells?

Sorry for late reply. I just want to change the color and the border lines so the users know that such fields will appear in the sheet summary. can this be done with vba?

But that is not a conditional format - that is just a style change.
2 easy ways to do this
either
- by vba
or
- create a new style in Excel and then you can just click on the style icon each time

Are the formats of these cells EXACTLY the same?

Sorry, i am lost😯. For me Conditional formating is, that when a cell complies with a certain condition, then something is applied to it (color or other characteristics) so in my case the condition is that if the cell is named then it should be colored.
So all cells that are a named range should be colored in blue. Some of them are numeroc, others are text an the color should be appied independently of the fact that they be empty or not.

Unfortunately , in using the word "Conditional", Microsoft chose to use a word that has a much wider in meaning to us than it does to Excel - that was not very helpful.

In the attached workbook I have created a new style called "Kev" (with borders and fill), as illustrated below.

Format any cells quickly by clicking on each cell in turn and then click on style "Kev"
If you hold the {CTRL} key down after selecting the first cell you can select more cells and format them all together with one click.

The fact that the cells are named is of no consequence to this solution

The next post will include a VBA solution which can use the named ranges

CellNewStyle1.jpg

CellNewStyle2.jpg

CellNewStyle3.jpg

@TudyBTH
Can you explain
- how to create a combined range and
- how to then apply formatting to it

thanks

@TudyBTH
But all defined names should has the same size, probably. I tested it a little and it works. Of course after you define all and close CF, and next open it again, names are showing as ranges.
This is faster than select every range independently one by one.

correction: can be different sizes

and a vba solution
Run macro in attached workbook with {CTRL} {t}

Sub formatNamedRanges()
'declare variables
    Dim n As Name, aSheet As String, aName As String
'loop through all names
    For Each n In ThisWorkbook.Names
    
    aSheet = n.RefersToRange.Parent.Name
    aName = n.Name
'apply formatting
    With Sheets(aSheet).Range(aName)
            With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThick
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0.399945066682943
                .PatternTintAndShade = 0
            End With
    End With
Next n

End Sub

And if you have already created a style as indicated in post#7

the VBA is reduced to this:

Sub formatNamedRanges1()

'declare variables
    Dim n As Name, aSheet As String, aName As String
'loop through all names
    For Each n In ThisWorkbook.Names
        aSheet = n.RefersToRange.Parent.Name
        aName = n.Name
'apply formatting
        Sheets(aSheet).Range(aName).Style = "Kev"
    Next n

End Sub