> 6 CONDITIONAL FORMATS

[Songoku]

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

 

[Pete_UK]

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

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

This gives you up to 30 conditional formats per cell.

Hope this helps.

Pete

 

[Gord Dibben]

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

 

[Lise]

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??

 

[Gord Dibben]

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

 

[Lise]

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??

 

[Gord Dibben]

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

 

[Lise]

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.