Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hi All
Thanks for looking, the help of this forum is always apreciated. I have the following bit of code: Sub Format1() ' ' Format1 Macro ' With Range("E26:H26") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub This adds the conditioanl formatting I want to row 26. I need this to also loop down to about row 2000. Is the best option: 1. can the 26's in my above code be set as a variable and then use a loop such as "For I = 26 to 2000". 2. set one line as above then copy and paste to the rest. I also had a little side question. When setting the formula1 condition in both conditional formatting as above and when you are are setting validation rules on a cell when you do it manually you can set the formaulla to refer to cell in which you are evaluating the validation or conditional formatting. Can you use the activecell variable to do this in VBA code. -- Regards and Thanks for any assistance. Francis Brown. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Assuming you want the IF statement to change rows accordingly, this worked
for me. Test it before permanent installation. Sub Format1() ' ' Format1 Macro Range("A26").Activate ' With Range("E26:H2000") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub "Francis Brown" wrote in message ... Hi All Thanks for looking, the help of this forum is always apreciated. I have the following bit of code: Sub Format1() ' ' Format1 Macro ' With Range("E26:H26") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub This adds the conditioanl formatting I want to row 26. I need this to also loop down to about row 2000. Is the best option: 1. can the 26's in my above code be set as a variable and then use a loop such as "For I = 26 to 2000". 2. set one line as above then copy and paste to the rest. I also had a little side question. When setting the formula1 condition in both conditional formatting as above and when you are are setting validation rules on a cell when you do it manually you can set the formaulla to refer to cell in which you are evaluating the validation or conditional formatting. Can you use the activecell variable to do this in VBA code. -- Regards and Thanks for any assistance. Francis Brown. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Thank you for the responce it was much appreciated and I'm going to streach
your indugence a little further. I should have relised that if on the excel interface when you select multiple rows for conditional formatting that it can work out the correct formulas for each cell then it would also do so in the VBA manner. I would now like to expand my example. I have set the code as this now: Sub Format1() ' ' Format1 Macro finalrow = Worksheets("INV Bookings").Range("D65536").End(xlUp).Row Range("A26").Activate With Range("E26").Resize(finalrow - 24, 4) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub With the above we have covered columns E,F,G and H for the extent of the data row's I need it also to go accross 7 sets of columns, the Next set being K,L,M and N, then missing two columns and then another 4 and so forth. Now I could just repeat the set of code I have arived at with your help 7 times changing the range and $H in each of the formula1 ref's. I just thought It must be more elegent to do it with a loop. for this line With Range("E26").Resize(finalrow - 24, 4) I thought some thing along the lines of for newcols = 1 to 7 With Range("E26").offset(0, 6 * newcols - 1).Resize(finalrow - 24, 4) 'code here next newcols What I cant figure out is what to do with the $H's in formula1 part of the conditional formatting code. -- Regards and Thanks for any assistance. Francis Brown. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
I will take a look tomorrow. I don't work with conditional format very
often so I will have to do some tests. But it should be practical to do. "Francis Brown" wrote in message ... Thank you for the responce it was much appreciated and I'm going to streach your indugence a little further. I should have relised that if on the excel interface when you select multiple rows for conditional formatting that it can work out the correct formulas for each cell then it would also do so in the VBA manner. I would now like to expand my example. I have set the code as this now: Sub Format1() ' ' Format1 Macro finalrow = Worksheets("INV Bookings").Range("D65536").End(xlUp).Row Range("A26").Activate With Range("E26").Resize(finalrow - 24, 4) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub With the above we have covered columns E,F,G and H for the extent of the data row's I need it also to go accross 7 sets of columns, the Next set being K,L,M and N, then missing two columns and then another 4 and so forth. Now I could just repeat the set of code I have arived at with your help 7 times changing the range and $H in each of the formula1 ref's. I just thought It must be more elegent to do it with a loop. for this line With Range("E26").Resize(finalrow - 24, 4) I thought some thing along the lines of for newcols = 1 to 7 With Range("E26").offset(0, 6 * newcols - 1).Resize(finalrow - 24, 4) 'code here next newcols What I cant figure out is what to do with the $H's in formula1 part of the conditional formatting code. -- Regards and Thanks for any assistance. Francis Brown. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Managed to get this sorted. Thanks for help.
I switched over to R1C1 cell refs so could loop numerically both ways. -- Regards and Thanks for any assistance. Francis Brown. "JLGWhiz" wrote: I will take a look tomorrow. I don't work with conditional format very often so I will have to do some tests. But it should be practical to do. "Francis Brown" wrote in message ... Thank you for the responce it was much appreciated and I'm going to streach your indugence a little further. I should have relised that if on the excel interface when you select multiple rows for conditional formatting that it can work out the correct formulas for each cell then it would also do so in the VBA manner. I would now like to expand my example. I have set the code as this now: Sub Format1() ' ' Format1 Macro finalrow = Worksheets("INV Bookings").Range("D65536").End(xlUp).Row Range("A26").Activate With Range("E26").Resize(finalrow - 24, 4) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub With the above we have covered columns E,F,G and H for the extent of the data row's I need it also to go accross 7 sets of columns, the Next set being K,L,M and N, then missing two columns and then another 4 and so forth. Now I could just repeat the set of code I have arived at with your help 7 times changing the range and $H in each of the formula1 ref's. I just thought It must be more elegent to do it with a loop. for this line With Range("E26").Resize(finalrow - 24, 4) I thought some thing along the lines of for newcols = 1 to 7 With Range("E26").offset(0, 6 * newcols - 1).Resize(finalrow - 24, 4) 'code here next newcols What I cant figure out is what to do with the $H's in formula1 part of the conditional formatting code. -- Regards and Thanks for any assistance. Francis Brown. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Glad you worked it out.
"Francis Brown" wrote in message ... Managed to get this sorted. Thanks for help. I switched over to R1C1 cell refs so could loop numerically both ways. -- Regards and Thanks for any assistance. Francis Brown. "JLGWhiz" wrote: I will take a look tomorrow. I don't work with conditional format very often so I will have to do some tests. But it should be practical to do. "Francis Brown" wrote in message ... Thank you for the responce it was much appreciated and I'm going to streach your indugence a little further. I should have relised that if on the excel interface when you select multiple rows for conditional formatting that it can work out the correct formulas for each cell then it would also do so in the VBA manner. I would now like to expand my example. I have set the code as this now: Sub Format1() ' ' Format1 Macro finalrow = Worksheets("INV Bookings").Range("D65536").End(xlUp).Row Range("A26").Activate With Range("E26").Resize(finalrow - 24, 4) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Please Book"", $H26=""Please Cancel""),TRUE,FALSE)" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(OR($H26=""Booked"", $H26=""Booked (Time Changed)""),TRUE,FALSE)" .FormatConditions(2).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF($H26=""Change"",TRUE,FALSE)" .FormatConditions(3).Interior.ColorIndex = 45 End With End Sub With the above we have covered columns E,F,G and H for the extent of the data row's I need it also to go accross 7 sets of columns, the Next set being K,L,M and N, then missing two columns and then another 4 and so forth. Now I could just repeat the set of code I have arived at with your help 7 times changing the range and $H in each of the formula1 ref's. I just thought It must be more elegent to do it with a loop. for this line With Range("E26").Resize(finalrow - 24, 4) I thought some thing along the lines of for newcols = 1 to 7 With Range("E26").offset(0, 6 * newcols - 1).Resize(finalrow - 24, 4) 'code here next newcols What I cant figure out is what to do with the $H's in formula1 part of the conditional formatting code. -- Regards and Thanks for any assistance. Francis Brown. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |