About Us

JLGWhiz[_2_]

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.

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I convert conditional formatting into explicit formatting?	Patrick Harris	Excel Discussion (Misc queries)	0	April 9th 09 12:00 AM
Conditional formatting--different formatting depending on cell con	Tammy S.	Excel Discussion (Misc queries)	3	March 30th 09 08:11 PM
Protect Cell Formatting including Conditional Formatting	Mick Jennings	Excel Discussion (Misc queries)	5	November 13th 07 05:32 PM
conditional Formatting based on cell formatting	Totom	Excel Worksheet Functions	3	January 20th 07 02:02 PM
Conditional Formatting that will display conditional data	BrainFart	Excel Worksheet Functions	1	September 13th 05 05:45 PM

Menu

About Us