hard time with If InStr(1, ..
 

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" ?

hard time with If InStr(1, ..
 

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("A1:D4")
set c = searchrange.find(what:="ACT",lookin:=xlvalues,look at:=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



"CC" wrote:

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" ?

hard time with If InStr(1, ..
 

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


"CC" wrote in message
...
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" ?

hard time with If InStr(1, ..
 

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

CC wrote:

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" ?

--

Dave Peterson