uisng two paramaters for conditional formatting / Limiting screen size

[Colin Hayes]

Hi

I have a couple of small questions :

1. The first one is to do with using more than one condition in
conditional formatting without having once countermand the other.

I'm using =MOD(ROW(),2)=0 to colour alternate rows down my worksheet.

I'm also trying to have negative numbers show in red by using formula ->
less then zero -> font red.

I can't , whatever I do , get this to work so that negative numbers will
be red on whichever row they appear , without changing the background
colour. Any suggestions?

2 . Also , I would like to limit the visible screen size to A1 - K27 ,
so that the visible screen won't scroll beyond these rows. Is there a
piece of code I can enter to do this?

Any help gratefully received.

Thanks.

 

[Gord Dibben]

Answer for #1

Don't use red background color for the alternate rows.

Try a lighter color and employ the regular Format>Cells>Number>Number red font
for negatives.

Answer for #2

You can set the allowable scrolling area using VBA code.

Since the scrollarea method does not stick between sessions you will have to
reset it each time you open the workbook.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

Adjust the sheetname and range to suit.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:K27"
End Sub

Or also in the Thisworkbook module to limit scrollarea on all sheets.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveSheet
.ScrollArea = "A1:K27"
End With
End Sub


Gord Dibben MS Excel MVP

 

[David Biddulph]

Don't forget that you can get negative numbers to appear in red without
using conditional formatting. Just use Format/ Cells, and choose an
appropriate format.
General;[Red]-General for example.

 

[Colin Hayes]

Hi Gord

OK thanks for your help. I've fixed the formatting issue now.

I managed to implement the code to into the Thisworkbook module using
this information I got from the net :

"To directly access ThisWorkbook module of the active workbook while in
Excel, right click on the Excel icon (top left next to File) and select
"View Code".

Is it possible to specify a range of sheets in the code , do you know?
Perhaps by separating each sheet name in the code somehow?

Also , I was wondering if the sheet itself could be specified in a
different way , other than by giving a name - so that if the sheet name
were changed , the scrolling restriction would still apply.

Thanks Gord

Best Wishes

 

[Gord Dibben]

Each sheet has a numbered order from left to right as well as a given sheetname.

You can refer to the order in an array.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1, 3, 6))
For Each Sheet In mysheets
Sheet.ScrollArea = "A1:K27"
Next
End Sub

If sheetname is changed, codename remains the same.

What I don't know is how to provide for moving the sheets into a new order, but
someone will leap in and explain that for us.

My klunky method would be to put sheetactivate code into each worksheet.

Private Sub Worksheet_Activate()
ScrollArea = "A1:K27"
End Sub


Gord