Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
uisng two paramaters for conditional formatting / Limiting screen size
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
uisng two paramaters for conditional formatting / Limiting screen size
Answer for #1
Don't use red background color for the alternate rows. Try a lighter color and employ the regular FormatCellsNumberNumber 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 On Sun, 20 May 2007 04:16:38 +0100, Colin Hayes wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
uisng two paramaters for conditional formatting / Limiting screen size
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. -- David Biddulph "Colin Hayes" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
uisng two paramaters for conditional formatting / Limiting screen size
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 In article , Gord Dibben <gorddibbATshawDOTca@?.? writes Answer for #1 Don't use red background color for the alternate rows. Try a lighter color and employ the regular FormatCellsNumberNumber 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 On Sun, 20 May 2007 04:16:38 +0100, Colin Hayes wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
uisng two paramaters for conditional formatting / Limiting screen size
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 On Sun, 20 May 2007 13:43:20 +0100, Colin Hayes wrote: 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 In article , Gord Dibben <gorddibbATshawDOTca@?.? writes Answer for #1 Don't use red background color for the alternate rows. Try a lighter color and employ the regular FormatCellsNumberNumber 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 On Sun, 20 May 2007 04:16:38 +0100, Colin Hayes wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work sheet size relative to screen size | Excel Discussion (Misc queries) | |||
Limiting size field MS query returns | Excel Discussion (Misc queries) | |||
How Do I Autofit To Screen Size | Excel Worksheet Functions | |||
How can I set the screen size to always display @ 110%- a default | Excel Discussion (Misc queries) | |||
why cant i change font size when i use conditional formatting? | Excel Discussion (Misc queries) |