[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Formatting of fields

JeffH

12/11/2006 9:34:00 PM

HI,

Does anyone know if it is possible to programmatically reset an excel
spreadsheet such that every third and fourth row are a different background
color but yet the first two rows are left untouched? I have a user that is
trying to format the first two rows with a default background and then the
third and fourth rows to be formatted with a green-bar background and
continue on in the spreadsheet using this technique. The problem here is
that if a row gets added into the spreadsheet, then the user needs to
reformat everything. I'm thinking that a macro could do it but I'm not well
versed in Excel macros.

Thanks in advance,
JeffH
2 Answers

Dave \Crash\ Dummy

12/11/2006 11:30:00 PM

0

Hi Jeff,

You can achieve this via conditional formatting. To do this:
.. select the range you want the formatting to apply to
.. use Format|ConditionalFormatting and:
. set 'Condition 1' to 'Formula is'
. key in the formula '=MOD(ROW()-1,4)>1' (without the quotes)
. use Format|Patterns to apply the greeen background
. click 'OK', 'OK'.
This formatting will automatically re-do the alternate shadings whenever
rows are added or deleted.

Cheers

--
macropod
[MVP - Microsoft Word]


"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:C66BF3F7-4337-4183-A05F-B2306D48698D@microsoft.com...
> HI,
>
> Does anyone know if it is possible to programmatically reset an excel
> spreadsheet such that every third and fourth row are a different
background
> color but yet the first two rows are left untouched? I have a user that
is
> trying to format the first two rows with a default background and then the
> third and fourth rows to be formatted with a green-bar background and
> continue on in the spreadsheet using this technique. The problem here is
> that if a row gets added into the spreadsheet, then the user needs to
> reformat everything. I'm thinking that a macro could do it but I'm not
well
> versed in Excel macros.
>
> Thanks in advance,
> JeffH
>
>

Paul Beard

12/11/2006 11:34:00 PM

0

"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:C66BF3F7-4337-4183-A05F-B2306D48698D@microsoft.com...
: HI,
:
: Does anyone know if it is possible to programmatically reset an excel
: spreadsheet such that every third and fourth row are a different
background
: color but yet the first two rows are left untouched? I have a user that
is
: trying to format the first two rows with a default background and then the
: third and fourth rows to be formatted with a green-bar background and
: continue on in the spreadsheet using this technique. The problem here is
: that if a row gets added into the spreadsheet, then the user needs to
: reformat everything. I'm thinking that a macro could do it but I'm not
well
: versed in Excel macros.
:
: Thanks in advance,
: JeffH
:

To do this programmatically try

Public Sub RowColor()
Dim c As Range

For Each c In ActiveSheet.Rows
If (c.Row - 1) Mod 4 > 1 Then
c.Interior.ColorIndex = 10
Else: c.Interior.ColorIndex = xlNone
End If
Next c

End Sub

The above changes all rows which may be too many. To limit the row count
you can dim c as an integer and change the for each line to for c = 1 to
number_of_rows_to_change