Almost correct, but not 'AND If '. only AND.

Also I hope that you are using Bob's code to ensure that the
cell is not in the Top row otherwise Offset(-1, 0) will create an error.

You can use
If cell.Row 1 Then
If cell.Interior.ColorIndex = 6 And InStr(1, cell.Offset(-1,
0).Formula, "=SUM") 0 Then
'code if this condition is met
End If
End If

Sharad
]
"J_J" wrote in message
...
Hi Sharad,
Sorry for keep asking on an on...
Is the syntax below also correct for such a command line? (I need to check
if the above cell over the yellow one has a formula including sum.)
' ----------
If cell.Interior.ColorIndex=36 AND If InStr(1, cell.Offset(-1, 0).Formula,
"=SUM") 0 Then
TIA
J_J



"Sharad Naik" wrote in message
...
It should be:

If cell.Interior.ColorIndex=36 AND If InStr(1, cell.Formula, "=SUM") 0
Then

Sharad

"J_J" wrote in message
...
Hi Bob and Sharad,
Can we add an additional criteria to the If line?
Such as...:
If cell.Interior.ColorIndex=36 AND cell.Contains.Formula "SUM" Then....
Regards


"Bob Phillips" wrote in message
...
Here is one way

Sub testcells()
Dim cell As Range

For Each cell In Selection
If cell.Interior.ColorIndex = 6 Then
If cell.Column = 1 Then
MsgBox "Can't get a previous in cell " &
cell.Address(False,
False)
Else
cell.Value = cell.Offset(0, -1).Value
End If
End If
Next cell

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J_J" wrote in message
...
So sorry about my mistake with the question!

The correct Q. should have been:
"I have a worksheet with some data on it. Some cells are colored
with green, yellow or red. I need a macro code that when executed
(assigned
to a button) will check for cells which are filled with "yellow"
color
and assign values to thouse cells in such a way that:
each of these cells will be filled with the values above these
cells!
(e.g.
say cell
C12, K22 and N55 are colored yellow, then C12 will display the
value
of cell C11; cell K22 will display the value of cell K21 and cell
N55
will
display the value in cell N54).
Is such an event possible?. Can anyone set an example?"
TIA
J_J



"J_J" wrote in message
...
Hi,
I have a worksheet with some data on it. Some region of cells are
colored
with green, yellow or red. I need a macro code that when executed
(assigned
to a button) will check for cells that are filled with "yellow"
color
only
and assign values to thouse cells in such a way that:
each of these cells will be filled with the value before it! (e.g.
say
cell
C12, K22 and N55 are colored yellow, then C12 will be filled with
the
value
of cell C11; cell K22 will get the value of cell K21 and N55 will
get
N54).
Is such an event possible?. Can anyone set an example?
Thanks in advance
J_J