| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Depending on Account cell is gray or white question.
I have account names and they are all in alphabetical order ascending. I want
to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
Do they really occur in blocks of three? If so you can use Format |
Conditional Formatting with Formula Is: =OR(MOD(ROW(),6)=1,MOD(ROW(),6)=2,MOD(ROW(),6)=3) and format to a grey pattern. Select all the row (drag down the row headers with the mouse) before using the Format | Conditional Formatting. If the blocks of 3 is not a valid observation, please let us know best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "LiveUser" wrote in message ... I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
It is not blocks of three. Anywhere from 1 to 50 or more.
Thank you. "Bernard Liengme" wrote: Do they really occur in blocks of three? If so you can use Format | Conditional Formatting with Formula Is: =OR(MOD(ROW(),6)=1,MOD(ROW(),6)=2,MOD(ROW(),6)=3) and format to a grey pattern. Select all the row (drag down the row headers with the mouse) before using the Format | Conditional Formatting. If the blocks of 3 is not a valid observation, please let us know best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "LiveUser" wrote in message ... I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
Conditional Formatting
=MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
I am fairly fresh at this so I have to ask what the process is on making this
happen? Thank you. "Teethless mama" wrote: Conditional Formatting =MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
Select the rows then FormatCondtional Formatting:Formula is:
Change the formula to this =MOD(RIGHT($A2,LEN($A2)-FIND(" ",$A2))+0,2)=1 Note the change to make A absolute by adding the $ sign then Format to Gray Pattern and OK your way out. Gord Dibben MS Excel MVP On Thu, 8 Nov 2007 07:34:04 -0800, LiveUser wrote: I am fairly fresh at this so I have to ask what the process is on making this happen? Thank you. "Teethless mama" wrote: Conditional Formatting =MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
I am sorry but for some reason I cannot get it to work...
"Gord Dibben" wrote: Select the rows then FormatCondtional Formatting:Formula is: Change the formula to this =MOD(RIGHT($A2,LEN($A2)-FIND(" ",$A2))+0,2)=1 Note the change to make A absolute by adding the $ sign then Format to Gray Pattern and OK your way out. Gord Dibben MS Excel MVP On Thu, 8 Nov 2007 07:34:04 -0800, LiveUser wrote: I am fairly fresh at this so I have to ask what the process is on making this happen? Thank you. "Teethless mama" wrote: Conditional Formatting =MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
No need to be sorry<g
Let's see if we can walk you though it. Where are you entering the formula? It must be entered in the Formula is: dialog box in FormatConditional FormattingCondition1 What rows have you pre-selected before entering the formula in CF? If the top cell is A1 then change all instances of $A2 to suit. Gord On Thu, 8 Nov 2007 12:49:02 -0800, LiveUser wrote: I am sorry but for some reason I cannot get it to work... "Gord Dibben" wrote: Select the rows then FormatCondtional Formatting:Formula is: Change the formula to this =MOD(RIGHT($A2,LEN($A2)-FIND(" ",$A2))+0,2)=1 Note the change to make A absolute by adding the $ sign then Format to Gray Pattern and OK your way out. Gord Dibben MS Excel MVP On Thu, 8 Nov 2007 07:34:04 -0800, LiveUser wrote: I am fairly fresh at this so I have to ask what the process is on making this happen? Thank you. "Teethless mama" wrote: Conditional Formatting =MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
That is exactly what I did. It still does not work. Would it have anything to
do with my version of Excel being 2000? "Gord Dibben" wrote: No need to be sorry<g Let's see if we can walk you though it. Where are you entering the formula? It must be entered in the Formula is: dialog box in FormatConditional FormattingCondition1 What rows have you pre-selected before entering the formula in CF? If the top cell is A1 then change all instances of $A2 to suit. Gord On Thu, 8 Nov 2007 12:49:02 -0800, LiveUser wrote: I am sorry but for some reason I cannot get it to work... "Gord Dibben" wrote: Select the rows then FormatCondtional Formatting:Formula is: Change the formula to this =MOD(RIGHT($A2,LEN($A2)-FIND(" ",$A2))+0,2)=1 Note the change to make A absolute by adding the $ sign then Format to Gray Pattern and OK your way out. Gord Dibben MS Excel MVP On Thu, 8 Nov 2007 07:34:04 -0800, LiveUser wrote: I am fairly fresh at this so I have to ask what the process is on making this happen? Thank you. "Teethless mama" wrote: Conditional Formatting =MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Depending on Account cell is gray or white question.
CF was introduced with Excel 97 so your version is OK.
I copied your data directly from your post and pasted to a new sheet. Entered the formula in CF and got the desired effect. Perhaps your data is not exactly as advertised? Do you have the <space as in Account 1 Account 2 Account 3 If not, post a sample of the actual data. Gord On Fri, 9 Nov 2007 09:10:05 -0800, LiveUser wrote: That is exactly what I did. It still does not work. Would it have anything to do with my version of Excel being 2000? "Gord Dibben" wrote: No need to be sorry<g Let's see if we can walk you though it. Where are you entering the formula? It must be entered in the Formula is: dialog box in FormatConditional FormattingCondition1 What rows have you pre-selected before entering the formula in CF? If the top cell is A1 then change all instances of $A2 to suit. Gord On Thu, 8 Nov 2007 12:49:02 -0800, LiveUser wrote: I am sorry but for some reason I cannot get it to work... "Gord Dibben" wrote: Select the rows then FormatCondtional Formatting:Formula is: Change the formula to this =MOD(RIGHT($A2,LEN($A2)-FIND(" ",$A2))+0,2)=1 Note the change to make A absolute by adding the $ sign then Format to Gray Pattern and OK your way out. Gord Dibben MS Excel MVP On Thu, 8 Nov 2007 07:34:04 -0800, LiveUser wrote: I am fairly fresh at this so I have to ask what the process is on making this happen? Thank you. "Teethless mama" wrote: Conditional Formatting =MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1 "LiveUser" wrote: I have account names and they are all in alphabetical order ascending. I want to gray a row of cells on every other account name change. Example of what I want: A Row Color Account 1 Gray Account 1 Gray Account 1 Gray Account 2 White Account 2 White Account 2 White Account 3 Gray Account 3 Gray Account 3 Gray I know I can control-click on every other one but we all know with large spreadsheets that can take forever. Is there a formula or something else that can do this automatically/quickly? I should mention, and I don't know if this will make a difference, but each account may have different data in the same row as the account name. For example: A B Row Color Account 1 Information 1 Gray Account 1 Information 2 Gray Account 1 Information 3 Gray Account 2 Information 4 White Account 2 Information 5 White Account 2 Information 6 White Account 3 Information 7 Gray Account 3 Information 8 Gray Account 3 Information 9 Gray Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL workbook is off-white, goes white in edit mode | Excel Discussion (Misc queries) | |||
| how do I apply 10% gray shade to a cell in Excel? | Excel Worksheet Functions | |||
| Gray cell lines | Excel Discussion (Misc queries) | |||
| White Space in a Cell | New Users to Excel | |||
| Help with function to sum values in a worksheet depending on account number | Excel Worksheet Functions | |||
Linear Mode
