Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Hello,
I'm wondering if it's possible to change the font colour of a range of cells depending on whether a different range of cells contain red text? For example cells U1:IU1 contain dates which turn the text red when they meet a ceratin condition. I want the data in the cells below this row (U2:IU197) to also change to red text if the top row is red. So basically if U30 contains red text I want the whole of column U30 to turn to red text. Can this be done? If so how? Any help would be very much appreciated as I've been struggling for a while!! :( Thank you in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
You need to apply the same conditional formatting criteria that you
have in row 1 to the rows below. You may need to use Formula Is rather than Cell Value Is in the first box of the DF dialogue. You can highlight the range and enter the criteria once, rather than do it for every cell individually. If you are still uncertain, post back with details of your current CF criteria. Hope this helps. Pete On Jun 19, 2:33*pm, Jennie wrote: Hello, I'm wondering if it's possible to change the font colour of a range of cells depending on whether a different range of cells contain red text? For example cells U1:IU1 contain dates which turn the text red when they meet a ceratin condition. I want the data in the cells below this row (U2:IU197) to also change to red text if the top row is red. So basically if U30 contains red text I want the whole of column U30 to turn to red text. Can this be done? If so how? Any help would be very much appreciated as I've been struggling for a while!! :( Thank you in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Thanks for the speedy response Pete!
I've tried this but it doesn't work. I think it might be becasue the top row has dates in it and the other cells just have 1 digit figures. The CF for the top row is If cell value is greater than or equal to =$g$1 then condition..... G1 is TODAY-365 Perhaps my initial way of working out what I want is better..... I basically need a formula to count the non blank cells within a rolling year. - The dates are in the top row, (U1:IU1) and the days/occassions sick are in the rows below (U2:IU2, U3:IU3 and so on) My initial formula: =SUMIF(($S$1:$IV$1=TODAY()-365)*S2:IV2) adds up all the figures (total days sick) correctly within the rolling 12 months, but I also need to count the non blank cells (occasions) for the same condition. i.e if someone has been off sick for 4 days in one week I want excel to count this as 1 rather than 4. I thought that changing 'SUMIF' to 'COUNTIF' would work, but it doesn't - hense the diffrent strategy! I've tried loads of different things but nothing seems to work. I'm well and truely stuck!!! :( Jennie "Pete_UK" wrote: You need to apply the same conditional formatting criteria that you have in row 1 to the rows below. You may need to use Formula Is rather than Cell Value Is in the first box of the DF dialogue. You can highlight the range and enter the criteria once, rather than do it for every cell individually. If you are still uncertain, post back with details of your current CF criteria. Hope this helps. Pete On Jun 19, 2:33 pm, Jennie wrote: Hello, I'm wondering if it's possible to change the font colour of a range of cells depending on whether a different range of cells contain red text? For example cells U1:IU1 contain dates which turn the text red when they meet a certain condition. I want the data in the cells below this row (U2:IU197) to also change to red text if the top row is red. So basically if U30 contains red text I want the whole of column U30 to turn to red text. Can this be done? If so how? Any help would be very much appreciated as I've been struggling for a while!! :( Thank you in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Select all the cells in the range U2:IU197, with U2 as the first cell
that you select (it won't be highlighted like the others). Then click on Format | Conditional Formatting and in the first box choose Formula Is rather than Cell Value Is. In the formula box you should enter: =U$1=$G$1 then click the Format button and choose Red from the Colour box. Click OK twice, and you should have what you want, as Excel will automaticlly adjust that formula to suit all the cells in the range. Hope this helps. Pete On Jun 19, 4:40*pm, Jennie wrote: Thanks for the speedy response Pete! I've tried this but it doesn't work. I think it might be becasue the top row has dates in it and the other cells just have 1 digit figures. The CF for the top row is If cell value is greater than or equal to =$g$1 * * * * * * then condition..... G1 is TODAY-365 Perhaps my initial way of working out what I want is better..... I basically need a formula to count the non blank cells within a rolling year. - The dates are in the top row, (U1:IU1) and the days/occassions sick are in the rows below (U2:IU2, U3:IU3 and so on) My initial formula: =SUMIF(($S$1:$IV$1=TODAY()-365)*S2:IV2) adds up all the figures (total days sick) correctly within the rolling 12 months, but I also need to count the non blank cells (occasions) for the same condition. i.e if someone has been off sick for 4 days in one week I want excel to count this as 1 rather than 4. I thought that changing 'SUMIF' to 'COUNTIF' would work, but it doesn't - hense the diffrent strategy! I've tried loads of different things but nothing seems to work. I'm well and truely stuck!!! :( Jennie "Pete_UK" wrote: You need to apply the same conditional formatting criteria that you have in row 1 to the rows below. You may need to use Formula Is rather than Cell Value Is in the first box of the DF dialogue. You can highlight the range and enter the criteria once, rather than do it for every cell individually. If you are still uncertain, post back with details of your current CF criteria. Hope this helps. Pete On Jun 19, 2:33 pm, Jennie wrote: Hello, I'm wondering if it's possible to change the font colour of a range of cells depending on whether a different range of cells contain red text? For example cells U1:IU1 contain dates which turn the text red when they meet a certain condition. I want the data in the cells below this row (U2:IU197) to also change to red text if the top row is red. So basically if U30 contains red text I want the whole of column U30 to turn to red text. Can this be done? If so how? Any help would be very much appreciated as I've been struggling for a while!! :( Thank you in advance- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Hi,
Please post the criteria you used in the CF in cells U1:IU1 Regards - Dave. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Hi Dave,
The CF in cells U1:IU1 is Formula Is =U1=TODAY()-365 Jennie "Dave" wrote: Hi, Please post the criteria you used in the CF in cells U1:IU1 Regards - Dave. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
I've tried this but for some reason excel isn't automatically adjusting the
formula to suit the other cells. It's keeping 'U1' throughout and therefore isn't working. I've tried it without '$' but with no success - again it keeps 'U1' in the formula. Do you have any other suggestions? I really appreciate your time and help. :) Jennie "Pete_UK" wrote: Select all the cells in the range U2:IU197, with U2 as the first cell that you select (it won't be highlighted like the others). Then click on Format | Conditional Formatting and in the first box choose Formula Is rather than Cell Value Is. In the formula box you should enter: =U$1=$G$1 then click the Format button and choose Red from the Colour box. Click OK twice, and you should have what you want, as Excel will automaticlly adjust that formula to suit all the cells in the range. Hope this helps. Pete On Jun 19, 4:40 pm, Jennie wrote: Thanks for the speedy response Pete! I've tried this but it doesn't work. I think it might be becasue the top row has dates in it and the other cells just have 1 digit figures. The CF for the top row is If cell value is greater than or equal to =$g$1 then condition..... G1 is TODAY-365 Perhaps my initial way of working out what I want is better..... I basically need a formula to count the non blank cells within a rolling year. - The dates are in the top row, (U1:IU1) and the days/occassions sick are in the rows below (U2:IU2, U3:IU3 and so on) My initial formula: =SUMIF(($S$1:$IV$1=TODAY()-365)*S2:IV2) adds up all the figures (total days sick) correctly within the rolling 12 months, but I also need to count the non blank cells (occasions) for the same condition. i.e if someone has been off sick for 4 days in one week I want excel to count this as 1 rather than 4. I thought that changing 'SUMIF' to 'COUNTIF' would work, but it doesn't - hense the diffrent strategy! I've tried loads of different things but nothing seems to work. I'm well and truely stuck!!! :( Jennie "Pete_UK" wrote: You need to apply the same conditional formatting criteria that you have in row 1 to the rows below. You may need to use Formula Is rather than Cell Value Is in the first box of the DF dialogue. You can highlight the range and enter the criteria once, rather than do it for every cell individually. If you are still uncertain, post back with details of your current CF criteria. Hope this helps. Pete On Jun 19, 2:33 pm, Jennie wrote: Hello, I'm wondering if it's possible to change the font colour of a range of cells depending on whether a different range of cells contain red text? For example cells U1:IU1 contain dates which turn the text red when they meet a certain condition. I want the data in the cells below this row (U2:IU197) to also change to red text if the top row is red. So basically if U30 contains red text I want the whole of column U30 to turn to red text. Can this be done? If so how? Any help would be very much appreciated as I've been struggling for a while!! :( Thank you in advance- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Hi,
Not sure if you'll have any more success than you're having with Pete's suggestions, but here goes: Select all cells in the range U2:IU197. Make sure you start the selection in cell U2. When all cells are selected, U2 should be the only one that looks different. With all that selected, open the CF window, select formula is, and enter: =U$1=TODAY()-365 Select the CF's you want. OK. I've tested this here, so it should work... Regards - Dave. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - coloured text
Yippeee!! It's now working! Thank you so much. I'm not sure what was wrong
with it before as I had tried this previously. In the end I deleted all the CF's and re-did them. That seemed to do the trick. :) Would it be possible for me to now count and sum the cells containing red text? Thank you to you and Pete for your time and assistance. You've saved me a lot of trial and error time! "Dave" wrote: Hi, Not sure if you'll have any more success than you're having with Pete's suggestions, but here goes: Select all cells in the range U2:IU197. Make sure you start the selection in cell U2. When all cells are selected, U2 should be the only one that looks different. With all that selected, open the CF window, select formula is, and enter: =U$1=TODAY()-365 Select the CF's you want. OK. I've tested this here, so it should work... Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to COUNT coloured in Conditional Formatted Cells | Excel Discussion (Misc queries) | |||
Counting Coloured Text | Excel Discussion (Misc queries) | |||
Coloured text | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) |