If then statement

[Todd Huttenstine]

Below is part of a code that works, but I need to make it
conditional. I would like to have the code perform this
function:

If the value "area1" is found anywhere in Range Z1:Z26,
perform the code.
Range("area1").Interior.ColorIndex = 32

How would I write this?

Thank you.
Todd Huttenstine

 

[Michael Hopwood]

Try:

'--------------------------------

Dim rng as range
set rng = activesheet.range("Z1:Z26")

dim c as range
for each c in rng.cells
if c.value = "area1" then
c.interior.colorindex = 32
else
c.interior.colorindex = 1 '??? or whatever
end if
next

'---------------------------------


That should give you a starting point.

 

[Dave Peterson]

One way:

with worksheets("sheet1")
if application.countif(.range("z1:z26"),"area1") > 0 then
.range("area1").interior.colorindex = 32
end if
end with

(I put area1 on sheet1. Modify if required.)

This actually looks for "area1" in the cell.
It won't react to "this is area1 here".

But you could use:
if application.countif(.range("z1:z26"),"*area1*") > 0 then

If the range gets large, then using .find might be quicker.

Dim FoundCell As Range
With Worksheets("sheet1")
Set FoundCell = Nothing
Set FoundCell = .Range("z1:z26").Find(what:="area1", _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
.Range("area1").Interior.ColorIndex = 32
End If
End With