How do I set up conditional formatting to give the greenbar effect?

[Saucer Man]

I want my excel workbook to have the greenbar effect starting with row 4. I
have 12 worksheets in the workbook. I would like the sheets to have this
effect as the data is being entered row by row daily. How can I do this?

 

[jvbelg]

I want my excel workbook to have the greenbar effect starting with row 4. I
have 12 worksheets in the workbook.  I would like the sheets to have this
effect as the data is being entered row by row daily.  How can I do this?

I am using the macro below since many years.
You select the area you want to be subject to the greenbar effect and
run the macro.
Change the ColorIndex number to get green.

Have fun!


~~~~~~~~~
'Make GreenBar spreadsheet
'
Sub GreenBar()

Application.ScreenUpdating = False

Dim iRows, iStartRow, iEndRow As Integer
Dim iColumns, iStartColumn, iEndColumn As Integer
Dim RCounter, CCounter As Integer

With Selection
iRows = .Rows.Count
iStartRow = .Row
iEndRow = .Row + iRows - 1

iColumns = .Columns.Count
iStartColumn = .Column
iEndColumn = .Column + iColumns - 1
End With

For RCounter = iStartRow + 1 To iEndRow Step 2
For CCounter = iStartColumn To iEndColumn
With Cells(RCounter, CCounter).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Next CCounter
Next RCounter

End Sub

 

[Saucer Man]

Thanks however that's not exactly what I am looking for. I want the entire
sheet to have the effect. I don't want the user to be required to select
text and then color it. Every row should have that effect even if they
haven't been used yet.

I want my excel workbook to have the greenbar effect starting with row 4.
I
have 12 worksheets in the workbook. I would like the sheets to have this
effect as the data is being entered row by row daily. How can I do this?

I am using the macro below since many years.
You select the area you want to be subject to the greenbar effect and
run the macro.
Change the ColorIndex number to get green.

Have fun!


~~~~~~~~~
'Make GreenBar spreadsheet
'
Sub GreenBar()

Application.ScreenUpdating = False

Dim iRows, iStartRow, iEndRow As Integer
Dim iColumns, iStartColumn, iEndColumn As Integer
Dim RCounter, CCounter As Integer

With Selection
iRows = .Rows.Count
iStartRow = .Row
iEndRow = .Row + iRows - 1

iColumns = .Columns.Count
iStartColumn = .Column
iEndColumn = .Column + iColumns - 1
End With

For RCounter = iStartRow + 1 To iEndRow Step 2
For CCounter = iStartColumn To iEndColumn
With Cells(RCounter, CCounter).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Next CCounter
Next RCounter

End Sub

 

[Jim Cone]

If you actually mean to only color a row if it has data in it then...
Select the rows and use this CF formula...
=COUNTA(1:1)>0

Otherwise just color the rows.
--
Jim Cone
Portland, Oregon USA


"Saucer Man"
<[email protected]>
wrote in message
Thanks however that's not exactly what I am looking for. I want the entire
sheet to have the effect. I don't want the user to be required to select
text and then color it. Every row should have that effect even if they
haven't been used yet.

 

[JLGWhiz]

Maybe you can adapt this to suit your purposes. Put this in the worksheet
code module. It uses the
Autoformat facility of Excel to set the patterns, so if you want to clear
the formatting manually, just do Edit>Clear>All. For VBA use Cells.Clear.
The built in format reduces the width of column A. so I inserted a line of
code to reset the width to standard. You might have to make additional
modifications, depending on your column width requirements.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("A1") Then
ActiveSheet.Cells.AutoFormat Format:=xlRangeAutoFormatList1
End If
Columns(1).ColumnWidth = 8.43
End Sub

 

[Saucer Man]

Thanks for the replies. I got it to work by using the following 2
conditional formulas...

=MOD(ROW(),2)=0
=MOD(ROW(),2)<>0

Then I selected a different color for each one.