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

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

Here you go:
Insert a module in vba project and copy below code:

Sub ColorFul()
Dim c
For Each c In Sheet1.UsedRange.Cells
If c.Interior.ColorIndex = 6 Then
c.Value = c.Offset(-1, 0).Value
End If
Next c
End Sub

Add a custom button and assign this macro to it.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

Thank you Sharad and Bob,
But unfortunately both codes didn'y worked for me !.
Could it be because the above cells are not visible on the screen?.


"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

I hid a column (FormatColumnHide), and that worked for me. How have you
set yours to not visible?
Did you also catch the NG wrap-around?
--

HTH

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


"J_J" wrote in message
...
Thank you Sharad and Bob,
But unfortunately both codes didn'y worked for me !.
Could it be because the above cells are not visible on the screen?.


"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

Well J_J then it might be possible that the Yellow colour you are
talking about is not actually Yellow (6) but some other shade.
Now cut and pase following code in to a module.

Sub WhatColour()
MsgBox Selection.Interior.ColorIndex
End Sub

Now select your one of the Yellow Cells,
Click on Tools Menu- Macro - Macros - Select 'WhatColour' to run it.

It will show the colourindex of the selected cell.

In the code given by me / Bob earlier change the colourindex accordinly.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bob, the original file that I am working on is not mine. There are buttons
on the left of the page with + or - signs on them. When pressed the rows
that are not visible becomes visible/hidden. With cells on the visible
yellow area, the user tries to sum-up all the data above each column (which
in normal are not visible because of the buttons mode).
J_J

"Bob Phillips" wrote in message
...
I hid a column (FormatColumnHide), and that worked for me. How have you
set yours to not visible?
Did you also catch the NG wrap-around?
--

HTH

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


"J_J" wrote in message
...
Thank you Sharad and Bob,
But unfortunately both codes didn'y worked for me !.
Could it be because the above cells are not visible on the screen?.


"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

Sharad,
Thanks for your help so far.
Unfortunately the example file that I am working on is at work and I can
only get back to it on Monday. But I'll let you & the NG know of the outcome
of your suggestions as soon as possible.
Regards
J_J

"Sharad" wrote in message
...
Well J_J then it might be possible that the Yellow colour you are
talking about is not actually Yellow (6) but some other shade.
Now cut and pase following code in to a module.

Sub WhatColour()
MsgBox Selection.Interior.ColorIndex
End Sub

Now select your one of the Yellow Cells,
Click on Tools Menu- Macro - Macros - Select 'WhatColour' to run it.

It will show the colourindex of the selected cell.

In the code given by me / Bob earlier change the colourindex accordinly.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

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

Thanks Sharad, I couldn't guess the "0" part!
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

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

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

You guys are great!...
Regards
J_J

"Sharad Naik" wrote in message
...
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

Hi Sharad and Bob,
I found my mistake. The yellow beeing used had an index of 36 but not 6.
This was the reason why both codes did not worked. Now both solutions works
like a charm.
You guys are great help.
Thanks
J_J

"Sharad" wrote in message
...
Well J_J then it might be possible that the Yellow colour you are
talking about is not actually Yellow (6) but some other shade.
Now cut and pase following code in to a module.

Sub WhatColour()
MsgBox Selection.Interior.ColorIndex
End Sub

Now select your one of the Yellow Cells,
Click on Tools Menu- Macro - Macros - Select 'WhatColour' to run it.

It will show the colourindex of the selected cell.

In the code given by me / Bob earlier change the colourindex accordinly.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!