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!
 

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top