can such a VBA code made?

[J_J]

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

 

[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

 

[Sharad]

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

 

[Bob Phillips]

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]

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]

I hid a column (Format>Column>Hide), 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)

 

[Sharad]

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

 

[J_J]

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

 

[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

 

[J_J]

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

 

[Sharad Naik]

It should be:

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

Sharad

 

[J_J]

Thanks Sharad, I couldn't guess the ">0" part!
J_J

 

[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]

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]

You guys are great!...
Regards
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
]

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



with
the will
get

 

[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