2 Attachment(s)
Colour Entire Row Base on the Value of Two Other Cells
Hi,
I'm having problems with conditional formatting. Conditional Formatting using the built in Excel formatting seems to require very long formulas for what I wish to achieve. I was hoping to get help coding with VBA instead.
I have attached a more exact example of my problem. Basically I want to colour each row based on the values in row "G" and "O'. They can be Red, Green, Blue or Black.
Attachment 13806
Below is my Excel Conditional Format effort for most of the conditions but does not include the ISBLANK in most cases. I am hoping that VBA will be simpler.
Code:
=IF(AND(ISBLANK($G$11),ISBLANK($O$11)),TRUE,FALSE)
=IF(AND(($G$11<=20),ISBLANK($O$11)),TRUE,FALSE)
=IF(AND(AND(($G$11>20),( $G$11<=50)),ISBLANK($O$11)),TRUE,FALSE)
=IF(AND(AND(($G$11>50),( $G$11<=100)),ISBLANK($O$11)),TRUE,FALSE)
=IF(AND(AND(($G$11>100)),ISBLANK($O$11)),TRUE,FALSE)
=IF(AND(ISBLANK($G$11),( $O$11<=7)),TRUE,FALSE)
=IF(AND(($G$11<=20),( $O$11<=7)),TRUE,FALSE)
=IF(AND($G$11>20, $G$11<=50, $O$11<=7),TRUE,FALSE)
=IF(AND($G$11>50, $G$11<=100, $O$11<=7),TRUE,FALSE)
=IF(AND($G$11>100, $O$11<=7),TRUE,FALSE)
=IF(AND(ISBLANK($G$11),( $O$11>7), $O$11<=30),TRUE,FALSE)
=IF(AND($G$11<=20, $O$11>7, $O$11<=30),TRUE,FALSE)
=IF(AND($G$11>20, $G$11<=50, $O$11<=30),TRUE,FALSE)
=IF(AND($G$11>50, $G$11<=100, $O$11>7, $O$11<=30),TRUE,FALSE)
=IF(AND($G$11>100, $O$11>7, $O$11<=30),TRUE,FALSE)
=IF(AND(ISBLANK($G$11),( $O$11>30), $O$11<=60),TRUE,FALSE)
=IF(AND($G$11<=20,$O$11>30, $O$11<=60),TRUE,FALSE)***
=IF(AND($G$11>20, $G$11<=50, $O$11>30, $O$11<=60),TRUE,FALSE)
=IF(AND($G$11>50, $G$11<=100, $O$11>30, $O$11<=60),TRUE,FALSE)
=IF(AND($G$11>100, $O$11>30, $O$11<=60),TRUE,FALSE)
=IF(AND(ISBLANK($G$11),( $O$11>60)),TRUE,FALSE)
=IF(AND(($G$11<=20),( $O$11>60)),TRUE,FALSE)
=IF(AND($G$11>20, $G$11<=50, $O$11>60),TRUE,FALSE)
=IF(AND(($G$11>100),( $O$11>60)),TRUE,FALSE)
=IF(AND($G$11>50, $G$11<=100, $O$11>60),TRUE,FALSE)
The columns with "TRUE" or "FALSE", only represent the formulas that I would use for the formatting. In reality they would be TASK's i.e. "Replace Oil Filter"
Many Thanks in Advance
1 Attachment(s)
Example of Excel Conditional Formatting Spreadsheet
Sorry, i didn't realise I could upload an Excel file. That's why there was only an image in my last post. I hope this attached file helps to explain my problem.
I realise the code I have posted is for the Excel Conditional Formatting and would really prefer to go to VBA if possible.
Thanks for any help in advance.
1 Attachment(s)
Example of Excel Conditional Formatting Spreadsheet
Hi and thanks for the kind help.
I've uploaded a modified spreadsheet that will hopefully explain what I'm trying, rather in vain, to achieve.
There are comments above the Columns which may clarify things. You will see that the only important Columns are the TASK column "A" , the HOURS column "G" and the DAYS column "O". All the other columns are to help me understand where I am and try to get my head around the problem. An important point to note is that HOURS are "running hours" not calendar hours.
snb.
Thanks for correcting my incorrect CF formula. I really do not want to use CF in Excel but rather VBA. The CF formulas where my first attempt at resolving my problem but I soon started to get lost when trying to apply them. If you change any of the values in the sample date HOURS or DAYS column, the cells do change TRUE and FALSE.
I hope this brings a little more clarity to my request.
Thanks again.