referring to range names

Hannah

Board Regular
Joined
Aug 20, 2002
Messages
51
Hi,

i want to make a reference to a range name in an if statement....something like this...

if nameoftherange = "hannah" then

etc etc

just don't know the format i'm supposed to use...any idea?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hope this is what you want. Suppose there is a cell named MyName and you want to know if it is the active cell:

If ActiveCell.Address = Range("MyName").Address Then
' Your code goes here
End If

Or

If ActiveCell.Address = [MyName].Address Then
' your code goes here
End If
 
Upvote 0
ah...can't seem to get it to work....this is what i've got....any chance you know what's going on???

Dim rng As range

For Each rng In Worksheets("home").range("d12:d20").CurrentRegion.Cells

If rng.Address = range("msubtotal").Address Then

rng.Select
Selection.Interior.ColorIndex = 40
Else
MsgBox "apparently not working"
End If
Next rng
End Sub

...also tried if rng = etc etc instead of rng.address but it didn't like that either...
 
Upvote 0
Two things. Why do you add CurrentRegion.Cells to your range. It appears you looking for a subtotal cell in cells D12:20

I used If rng.Address = Range("msubtotal").Address Then
rng.Select

Additionally, your error message will pop up every time msubtotal.address is not the valid cell.

This is the way I did it to color the cell named msubtotal

Sub hannah()
Dim rng As Range
For Each rng In Worksheets("home").Range("d12:d20")
If rng.Address = Range("msubtotal").Address Then
rng.Select
Selection.Interior.ColorIndex = 40
End If
Next rng
End Sub
This message was edited by hedrijw on 2002-08-29 00:00
 
Upvote 0
Here's a way to color all cells on a sheet that are on the right of a cell containing the text Subtotal. I've used your worksheet name ("home"), and the color you chose:

Sub Mark_Subtotals()
Set myarea = Worksheets("home").UsedRange
With myarea
Set c = .Find("Subtotal", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Range(c.Address).Offset(0, 1).Interior.ColorIndex = 40
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address<> firstAddress
End If
End With
End Sub


The advantage of the above method is that it uses the Find method instead of looping through all cells on the sheet.
This message was edited by hedrijw on 2002-08-29 06:33
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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