Function Help

K

kmwhitt

I would like to design a function that will select a range of cells and
assign an alternating color (light gray & no fill) to the rows within that
selection after a copy, cut or paste operation occurs. Anyone have any ideas?

Thanks,

Kevin
 
R

Rodrigo Ferreira

Did you try Conditional Format using formula?
Something like this: "MOD(ROW();2)=1"

Rodrigo Ferreira
 
K

kmwhitt

Yes, I did. I even recorded a macro:

Sub Color_Band()
Range("A16:p555").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Range("A16").Select
End Sub

Where would I insert this within the worksheet's code to insure it is run
everytime a copy, cut or paste event takes place. In fact, I only want it to
happen after a copy, cut or paste event occurs.
 
R

RagDyer

Since you said *after* a copy, cut or paste operation, you might try this:

While the range is *still* selected from the paste,
<Format> <Auto Format>
And pick one of the styles that you see there.

You could also use Conditional Formatting, which will *retain* the
formatting even if you subsequently insert or delete rows.

While the range is *still* selected from the paste,
<Format> <Conditional Formatting>,
Change "Cell Value Is" to
"Formula Is",
And enter this formula to shade *even* numbered rows:

=MOD(ROW(),2)=0

OR this one for *odd* numbered rows:

=MOD(ROW(),2)=1

Then click on "Format", and choose whatever you like.
 
K

kmwhitt

Hi RagDyer:

I'm not following you. I am creating a pricing utility. The user may need
to copy a row of data (or just a few cells) and paste it into another line
item. When this happens, the alternating colored rows get all bungled up.
How can I make Excel keep the format in this range constant no matter what
type of cut, copy or paste event takes place?

I was thinking a function that runs under _change that resets the
conditional formatting you suggested below. Any ideas?

Thanks,

Kevin
 
R

RagDyer

First of all, I can't help you with anything pertaining to code or VBA.

But as far as using the Conditional Formatting type of banding, if you
"PasteSpecial" - "Values" (so no formats of the copied cells will override
the banding format), the Conditional Formatting type of banding will remain
in force through the pasting process.
 
K

kmwhitt

Thanks, RagDyer:

The trouble with pastespecial is getting people to actually use it and avoid
messing up the format.....
 
S

Sinus Log

kmwhitt said:
Hi RagDyer:

I'm not following you. I am creating a pricing utility. The user may need
to copy a row of data (or just a few cells) and paste it into another line
item. When this happens, the alternating colored rows get all bungled up.
How can I make Excel keep the format in this range constant no matter what
type of cut, copy or paste event takes place?

I was thinking a function that runs under _change that resets the
conditional formatting you suggested below. Any ideas?

Thanks,

Kevin
Click to expand...

While trying to solve a problem somewhat different from
yours, I came up with this after searching Google groups for
the string:
excel "paste event"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then Exit Sub
Select Case Application.CutCopyMode
Case Is = xlCopy
x = 1
Case Is = xlCut
Application.CutCopyMode = False
MsgBox "Action cancelled"
End Select
End Sub

I am still working on it. You can't use it as it is, but you
might wish to modify it to suit your needs. Whenever x=1,
you would have to modify Copy/Paste to
Copy/PasteSpecial/Values. I've seen several threads dealing
with that (including this one).
 
Top