Range Names and Conditional Formatting

MartinSmith

New Member
Joined
Dec 1, 2015
Messages
22
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top