A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Formatting Separate Rows



 
 
Thread Tools Display Modes
  #1  
Old September 22nd, 2008, 08:30 PM posted to microsoft.public.excel.worksheet.functions
Pete
external usenet poster
 
Posts: 384
Default Conditional Formatting Separate Rows

I need to set-up three formali in each row of a 1000 row spreadsheet. The
three forumli a

=A4="x" then B4 : P4 strickthrough, green font
=B4Today() B4:P4 Font Red
=R4=True B4:P4 Font Purple Italic.

I enter that in Row 4 , now I need to repeat this for every row, but every
attempt fails to change the row identifier- thus every row ends up refering
to row 4 only. Hope do I copy this instead hand writing each row- 1000 rows?
  #2  
Old September 22nd, 2008, 09:34 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Conditional Formatting Separate Rows

Just go into Conditional Formatting again for that cell and check that
Excel has not added $ in front of the 4 in the formulae - if it has,
then remove them. Then you can repeat that CF for other cells by using
the Format Painter - if you double-click on the icon you can then
apply the format to many other cells by means of a single-click, and
then press Esc when you have finished.

Hope this helps.

Pete

On Sep 22, 8:30*pm, Pete wrote:
I need to set-up three formali in each row of a 1000 row spreadsheet. *The
three forumli a *

=A4="x" then B4 : P4 strickthrough, green font
=B4Today() B4:P4 Font Red
=R4=True B4:P4 Font Purple Italic.

I enter that in Row 4 , now I need to repeat this for every row, but every
attempt fails to change the row identifier- thus every row ends up refering
to row 4 only. *Hope do I copy this instead hand writing each row- 1000 rows?


  #3  
Old September 23rd, 2008, 08:47 PM posted to microsoft.public.excel.worksheet.functions
Pete
external usenet poster
 
Posts: 384
Default Conditional Formatting Separate Rows

Unfortunately, I does not work totally. The designated rows change (the rows
which the formats effect), however the formula stays the same for every row.
That is not what we want.

I need to have every row effect the rows designated, so if an x is in A4,
only cells b44 are effected. And if R7 = True, then only cells b77 are
effected. Etc.

Each row stands alone as a conditional format, but I can't find a way to
make one row correct for our conditional formate and then copy that to each
of the 1000 lines I need without having to edit every row individually. I
don't have that much time.

Thanks, Pete -US.

"Pete_UK" wrote:

Just go into Conditional Formatting again for that cell and check that
Excel has not added $ in front of the 4 in the formulae - if it has,
then remove them. Then you can repeat that CF for other cells by using
the Format Painter - if you double-click on the icon you can then
apply the format to many other cells by means of a single-click, and
then press Esc when you have finished.

Hope this helps.

Pete

On Sep 22, 8:30 pm, Pete wrote:
I need to set-up three formali in each row of a 1000 row spreadsheet. The
three forumli a

=A4="x" then B4 : P4 strickthrough, green font
=B4Today() B4:P4 Font Red
=R4=True B4:P4 Font Purple Italic.

I enter that in Row 4 , now I need to repeat this for every row, but every
attempt fails to change the row identifier- thus every row ends up refering
to row 4 only. Hope do I copy this instead hand writing each row- 1000 rows?



  #4  
Old September 23rd, 2008, 09:49 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Conditional Formatting Separate Rows

Highlight the block of cells B4:P1000 with B4 being the active cell.
Click on Format | Conditional Formatting, select Formula Is in the
first box and use this formula:

=$A4="x"

then click the Format button and set the format to Strikethrough,
Green font. Click OK, then Add. This time the formula will be:

=$B4TODAY()

then set the format to Red font. Click OK and then Add again, and use
this formula:

=$R4=TRUE

then choose your purple italic font. Click OK twice to exit the
dialogue box, and you will now have set it up for all those rows as
Excel will adjust the row (but not the column because of the $ symbol
in front) to suit.

Hope this helps.

Pete

On Sep 23, 8:47*pm, Pete wrote:
Unfortunately, I does not work totally. *The designated rows change (the rows
which the formats effect), however the formula stays the same for every row. *
That is not what we want.

I need to have every row effect the rows designated, so if an x is in A4,
only cells b44 are effected. *And if R7 = True, then only cells b77 are
effected. Etc.

Each row stands alone as a conditional format, but I can't find a way to
make one row correct for our conditional formate and then copy that to each
of the 1000 lines I need without having to edit every row individually. * I
don't have that much time.

Thanks, Pete -US.



"Pete_UK" wrote:
Just go into Conditional Formatting again for that cell and check that
Excel has not added $ in front of the 4 in the formulae - if it has,
then remove them. Then you can repeat that CF for other cells by using
the Format Painter - if you double-click on the icon you can then
apply the format to many other cells by means of a single-click, and
then press Esc when you have finished.


Hope this helps.


Pete


On Sep 22, 8:30 pm, Pete wrote:
I need to set-up three formali in each row of a 1000 row spreadsheet. *The
three forumli a *


=A4="x" then B4 : P4 strickthrough, green font
=B4Today() B4:P4 Font Red
=R4=True B4:P4 Font Purple Italic.


I enter that in Row 4 , now I need to repeat this for every row, but every
attempt fails to change the row identifier- thus every row ends up refering
to row 4 only. *Hope do I copy this instead hand writing each row- 1000 rows?- Hide quoted text -


- Show quoted text -


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.