hard time with If InStr(1, ..

[CC]

With this expression the program look the first cell of 3 cell an if find
"ACT" or "DEACT" paint this 3 cells of Yelow .



If ThisWorkbook.Sheets("list").DrawingObjects("Check Box 9").Value = xlOn Then
If InStr(1, Sheets("Grid").Cells(I, L).Value, "ACT") > 0 Or _
InStr(1, Sheets("Grid").Cells(I, L).Value, "DEACT") > 0 Then
'interior color yellow For Alarms: WARN and ALT
Sheets("Grid").Cells(I, L).Interior.ColorIndex = 6
Sheets("Grid").Cells(I, L + 1).Interior.ColorIndex = 6
Sheets("Grid").Cells(I + 1, L).Interior.ColorIndex = 6
Sheets("Grid").Cells(I + 1, L + 1).Interior.ColorIndex = 6


But what I need is paint on yellow if he find a word "(CC)" in the third
cell

Well my question is . can I have a search or find instead of Value,
"ACT" ?

 

[Joel]

you can use find but you need to set lookat:

LookAt Optional Variant. Can be one of the following XlLookAt constants:
xlWhole or xlPart.

set searchrabge = sheets("Grid").range("A14")
set c = searchrange.find(what:="ACT",lookin:=xlvalues,lookat:=xlPart)
if not c is nothing then
'add code if found

c.Interior.ColorIndex = 6
c.offset(0,1).Interior.ColorIndex = 6
c.offset(1,0).Interior.ColorIndex = 6
c.offset(1,1).Interior.ColorIndex = 6
else
'add code if not found
end if

 

[Bernie Deitrick]

I'm not sure what you mean by third cell or three cells, since your code is coloring FOUR cells, but
you could extend your conditional like so:

If InStr(1, Sheets("Grid").Cells(I, L).Value, "ACT") > 0 Or _
InStr(1, Sheets("Grid").Cells(I, L).Value, "DEACT") > 0 Or _
InStr(1, Sheets("Grid").Cells(I, L+1).Value, "CC") > 0 Then


That would look for CC in second column, first row. Also, you can use one line (instead of four) to
do the coloring:

Sheets("Grid").Cells(I, L).Resize(2,2).Interior.ColorIndex = 6


HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

And I'd drop the code for DEACT, too--since DEACT already has ACT in it.