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

Bob Phillips has an add-in you can download from here:

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 <Song...@discussions.microsoft.com> 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

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 <(E-Mail Removed)>
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
>
>
>

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 here:
>
> 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 <Song...@discussions.microsoft.com> 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
>
>

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 Tools>Add-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 <(E-Mail Removed)> 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??

Thanks for the speedy response Gord - have checked Tools>Add-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 Tools>Add-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 <(E-Mail Removed)> 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??
>
>

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 <(E-Mail Removed)> wrote:

>Thanks for the speedy response Gord - have checked Tools>Add-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 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 <(E-Mail Removed)> wrote:
>
> >Thanks for the speedy response Gord - have checked Tools>Add-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??
>
>