Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting/VBA
FAO Dav,
Sorry, but none of the suggestions you put forward worked. I thought I would re-post I have a spreadsheet containing some information that I need to apply a couple of conditional formats too, however this may have to be done using VBA as I think it may be impossible to do this going through the normal conditional format route. In cell B2 there is a date (formatted to mmm) and in cells E4 to P4 I have headed the columns with the months, April to March (again formatted to mmm) i.e. a financial year. In cells E6 to Q26 is held all the financial information for each month. What I am trying to do, is change all the zero values to a gray font. I also want to apply a yellow fill colour to ALL the values, i.e. zero upwards, if they are in a column where the heading (E4 to P4) is greater than the current month (B2). Where the column heading is less that the value in B2 (i.e. where the month has passed), I want no fill colour but I still want the zero values to have a gray font. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting/VBA
You can do this with conditional formatting and three conditions. Select
E6:Q6 and select Format-Conditional Formatting. Use "Formula Is" for all three conditions, and use the following formulas. =(E6=0)*(E$4$B$2) Format to grey font and yellow background =(E6=0)*(E$4<=$B$2) Format to grey font =(E$4$B$2) Format to yellow background "Chris Waller" wrote: FAO Dav, Sorry, but none of the suggestions you put forward worked. I thought I would re-post I have a spreadsheet containing some information that I need to apply a couple of conditional formats too, however this may have to be done using VBA as I think it may be impossible to do this going through the normal conditional format route. In cell B2 there is a date (formatted to mmm) and in cells E4 to P4 I have headed the columns with the months, April to March (again formatted to mmm) i.e. a financial year. In cells E6 to Q26 is held all the financial information for each month. What I am trying to do, is change all the zero values to a gray font. I also want to apply a yellow fill colour to ALL the values, i.e. zero upwards, if they are in a column where the heading (E4 to P4) is greater than the current month (B2). Where the column heading is less that the value in B2 (i.e. where the month has passed), I want no fill colour but I still want the zero values to have a gray font. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting/VBA
FAO Sloth,
That was brilliant! Thanks again for your help, much appreciated. Chris "Sloth" wrote: You can do this with conditional formatting and three conditions. Select E6:Q6 and select Format-Conditional Formatting. Use "Formula Is" for all three conditions, and use the following formulas. =(E6=0)*(E$4$B$2) Format to grey font and yellow background =(E6=0)*(E$4<=$B$2) Format to grey font =(E$4$B$2) Format to yellow background "Chris Waller" wrote: FAO Dav, Sorry, but none of the suggestions you put forward worked. I thought I would re-post I have a spreadsheet containing some information that I need to apply a couple of conditional formats too, however this may have to be done using VBA as I think it may be impossible to do this going through the normal conditional format route. In cell B2 there is a date (formatted to mmm) and in cells E4 to P4 I have headed the columns with the months, April to March (again formatted to mmm) i.e. a financial year. In cells E6 to Q26 is held all the financial information for each month. What I am trying to do, is change all the zero values to a gray font. I also want to apply a yellow fill colour to ALL the values, i.e. zero upwards, if they are in a column where the heading (E4 to P4) is greater than the current month (B2). Where the column heading is less that the value in B2 (i.e. where the month has passed), I want no fill colour but I still want the zero values to have a gray font. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |