Macro with IF function

[ianripping]

I have this code: -

Dim ange1
ange1 = ActiveSheet.Range("m140").Value
Set rng = Range(ange1)
rng.ClearContents
rng.Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic

Basically it looks at the range entered in m140 and uses that range t
delete any records in that range.

I would like to add an IF function that says if the range is c138:y13
then do nothing, otherwise opperate as normal.

Can anyone help me

 

[Tom Ogilvy]

Dim ange1
Dim rng as Range
ange1 = ActiveSheet.Range("m140").Value
Set rng = Range(ange1)
if not rng.Address(0,0) = "C138:Y138" then
rng.ClearContents
rng.Select
With Selection.Interior
ColorIndex = 2
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End if

If you mean that if the address included any part of C138:Y138, then do
nothing, use
Dim ange1
Dim rng as Range
ange1 = ActiveSheet.Range("m140").Value
Set rng = Range(ange1)
if Intersect(rng,Range("C138:Y138")) is nothing then
rng.ClearContents
rng.Select
With Selection.Interior
ColorIndex = 2
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End if

 

[ianripping]

No I meant that if the cell "m140" had the range B138:Y138 as its value
then the macro stops and doesn't clear that range of cells.

I tried this:-

Dim ange1
Dim rng as Range
ange1 = ActiveSheet.Range("m140").Value
Set rng = Range(ange1)
if not rng.Address(0,0) = "B138:Y138" then
rng.ClearContents
rng.Select
With Selection.Interior
ColorIndex = 2
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End if

but it still cleared the range

 

[Tom Ogilvy]

I understood that and provided a solution

Sub Tester1()
Dim ange1
Dim rng As Range
ange1 = ActiveSheet.Range("m140").Value
Set rng = Range(ange1)
Debug.Print rng.Address(0, 0), rng.Address(0, 0) = "B138:Y138"
If Not rng.Address(0, 0) = "B138:Y138" Then
rng.ClearContents
rng.Select
With Selection.Interior
ColorIndex = 2
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End With
End If

End Sub

worked fine for me.

so
A) you have typing errors.

B) m140 doesn't actually contain B138:Y138 (it may have leading or
trailing spaces)

C) that isn't actually the code you are using.

d) some other obtuse thing I haven't thought of. However, the code works
for the situation stated.

 

[mudraker]

One other suggestion as to why it may not be working correctly.


IF tests are case sensitive.

"B138:Y138" is not the same as "b138:y138

 

[Tom Ogilvy]

True that they are case sensitive, but not an issue here that I can see

If Not rng.Address(0, 0) = "B138:Y138" Then

even if it is lower case in the cell (m140), both sides of the equality sign
are uppercase.

and the only other location that uses the string in the cell, well there it
is irrelavant:

ange1 = ActiveSheet.Range("m140").Value
Set rng = Range(ange1)

 

[ianripping]

thanks tom worked a treat