6 CONDITIONAL FORMATS
 

Hi

I have a spreadsheet which contains rosters N for nights D for days OTN for
ovedrtime nights OTD for overtime days AL for anual leave, S for sick
conditional formating will allow me to set three of these conditions. How can
I do this programmatically as to perform the condition and cell colouring?

any help appreciated

6 CONDITIONAL FORMATS
 

Bob Phillips has an add-in you can download from he

http://www.xldynamic.com/source/xld.....Download.html

This gives you up to 30 conditional formats per cell.

Hope this helps.

Pete

On Apr 9, 2:08*pm, Songoku wrote:
Hi

I have a spreadsheet which contains rosters N for nights D for days OTN for
ovedrtime nights OTD for overtime days AL for anual leave, S for sick
conditional formating will allow me to set three of these conditions. How can
I do this programmatically as to perform the condition and cell colouring?

any help appreciated

6 CONDITIONAL FORMATS
 

You could use sheet event code to change the colors.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C4:IR30")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module. Make any edits then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Wed, 9 Apr 2008 06:08:00 -0700, Songoku
wrote:

Hi

I have a spreadsheet which contains rosters N for nights D for days OTN for
ovedrtime nights OTD for overtime days AL for anual leave, S for sick
conditional formating will allow me to set three of these conditions. How can
I do this programmatically as to perform the condition and cell colouring?

any help appreciated

6 CONDITIONAL FORMATS
 

Hi Pete

Saw your reply on this one which is great - have downloaded but cannot
locate button/area/icon to enable me to action the options - did you have any
problems??
--
Thanks

Lise (Aussie)


"Pete_UK" wrote:

Bob Phillips has an add-in you can download from he

http://www.xldynamic.com/source/xld.....Download.html

This gives you up to 30 conditional formats per cell.

Hope this helps.

Pete

On Apr 9, 2:08 pm, Songoku wrote:
Hi

I have a spreadsheet which contains rosters N for nights D for days OTN for
ovedrtime nights OTD for overtime days AL for anual leave, S for sick
conditional formating will allow me to set three of these conditions. How can
I do this programmatically as to perform the condition and cell colouring?

any help appreciated

6 CONDITIONAL FORMATS
 

After downloading you must un-zip and install the *.xla file in your
Office\Library folder.

If installed properly and CFPlus add-in is loaded through ToolsAdd-ins, an
extra menu item will appear left of Help

Look for "xld Tools"


Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 20:37:00 -0700, Lise wrote:

Hi Pete

Saw your reply on this one which is great - have downloaded but cannot
locate button/area/icon to enable me to action the options - did you have any
problems??

6 CONDITIONAL FORMATS
 

Thanks for the speedy response Gord - have checked ToolsAdd-ins and CFPlus
is ticked and listed however it is not showing left of help or anwhere on the
sheet - what an earth can I be doing??
--
Thanks

Lise (Aussie)


"Gord Dibben" wrote:

After downloading you must un-zip and install the *.xla file in your
Office\Library folder.

If installed properly and CFPlus add-in is loaded through ToolsAdd-ins, an
extra menu item will appear left of Help

Look for "xld Tools"


Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 20:37:00 -0700, Lise wrote:

Hi Pete

Saw your reply on this one which is great - have downloaded but cannot
locate button/area/icon to enable me to action the options - did you have any
problems??

6 CONDITIONAL FORMATS
 

Are you running Excel 2007?

I don't think CFPlus integrates with 2007 or Vista OS but Bob could tell us.

2007 is not listed on Bob's site as an acceptable platform.

I use 2003 and CFPlus adds the menuitem xld Tools as designed.


Gord

On Thu, 3 Jul 2008 14:34:00 -0700, Lise wrote:

Thanks for the speedy response Gord - have checked ToolsAdd-ins and CFPlus
is ticked and listed however it is not showing left of help or anwhere on the
sheet - what an earth can I be doing??

6 CONDITIONAL FORMATS
 

Thanks Gord - Have no Idea what I had done :-( - asked our comp guru to check
and hey presto all going as you said - Wonderful. Have a great weekend.
--
Thanks

Lise (Aussie)


"Gord Dibben" wrote:

Are you running Excel 2007?

I don't think CFPlus integrates with 2007 or Vista OS but Bob could tell us.

2007 is not listed on Bob's site as an acceptable platform.

I use 2003 and CFPlus adds the menuitem xld Tools as designed.


Gord

On Thu, 3 Jul 2008 14:34:00 -0700, Lise wrote:

Thanks for the speedy response Gord - have checked ToolsAdd-ins and CFPlus
is ticked and listed however it is not showing left of help or anwhere on the
sheet - what an earth can I be doing??