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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

hedrijw

Board Regular
Joined
Jun 17, 2002
Messages
210
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
 

Hannah

Board Regular
Joined
Aug 20, 2002
Messages
51
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...
 

hedrijw

Board Regular
Joined
Jun 17, 2002
Messages
210
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
 

Hannah

Board Regular
Joined
Aug 20, 2002
Messages
51

ADVERTISEMENT

will test out tomorrow (5:03.....too much overtime for me...) thanks!!!
 

hedrijw

Board Regular
Joined
Jun 17, 2002
Messages
210
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
 

Hannah

Board Regular
Joined
Aug 20, 2002
Messages
51
fantastic......

you've certainly lowered my stress levels for today...
 

Forum statistics

Threads
1,147,844
Messages
5,743,512
Members
423,800
Latest member
IuneKeiki

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
Top