Range Names and Conditional Formatting

[MartinSmith]

Hi Newbie here

I have a range name which I would like to format using conditional formatting.

When I change the cells that the range name refers to I want the it to format the new range.

This might not be the easiest way to do it so I'm open to alternative suggestions.

Essentially what I would like is a range name 'Gateway1' to have a grey background and when I alter the cells it refers to the background will automatically format.

Looking forward to your suggestions!

 

[Fishboy]

Hi Newbie here

I have a range name which I would like to format using conditional formatting.

When I change the cells that the range name refers to I want the it to format the new range.

This might not be the easiest way to do it so I'm open to alternative suggestions.

Essentially what I would like is a range name 'Gateway1' to have a grey background and when I alter the cells it refers to the background will automatically format.

Looking forward to your suggestions!

Hi Martin, welcome to the boards.

Without explicit details I have had to go off script a little here and improvise. You did not specify if VBA was an option so I have assumed that it is fine to use. The following code is added to a standard module and applied to a button to run.

In effect this code checks to see if named range GATEWAY1 exists and if not it creates it and fills it grey. If the range does already exist it clears out the current grey fill, removes the GATEWAY1 range and then recreates it based on your currently selected cells before filling the newly created range grey.

Sub RangeUpdater()
' Define variable
Dim rRangeCheck As Range
' Error handling
On Error Resume Next
' Check to see if GATEWAY1 range exists
    Set rRangeCheck = Range("GATEWAY1")
        On Error GoTo 0
' If GATEWAY1 range does not exist
            If rRangeCheck Is Nothing Then
' Creates new GATEWAY1 range based on current selection
                        Selection.Name = "GATEWAY1"
' Fills GATEWAY1 range grey
                            With Range("GATEWAY1")
                                .Interior.ColorIndex = 16
                            End With
' Else if GATEWAY1 range does already exist
            Else
' Clears current fill of existing GATEWAY1 range
                With Range("GATEWAY1")
                    .Interior.ColorIndex = xlNone
                End With
' Deletes exisiting GATEWAY1 range
                    ActiveWorkbook.Names("GATEWAY1").Delete
' Creates new GATEWAY1 range based on current selection
                        Selection.Name = "GATEWAY1"
' Fills GATEWAY1 range grey
                            With Range("GATEWAY1")
                                .Interior.ColorIndex = 16
                            End With
            End If
End Sub

 

[MartinSmith]

Thanks Fishboy, that seems to be the solution!

A great help

Martin

 

[Fishboy]

Thanks Fishboy, that seems to be the solution!

A great help

Martin

Happy to help mate, thanks for the feedback.