If then statement
 

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

If then statement
 

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.

--
Michael Hopwood (Phobos)


"Todd Huttenstine" wrote in message
...
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

If then statement
 

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

Todd Huttenstine wrote:

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

--

Dave Peterson