VBA Cond. Formatting, two sheets

[louww]

Hi,

Can anyone please help!

I've tried to apply my limited knowledge on this and can't find the right answer:

Sheet1 is a 2011 calender, sheet two is an activity list with 3 named ranges (in columns, e.g. courses, events, holidays).
Sheet 1 has conditional formatting for the three ranges with this formula for aeach condition:
=AND(E16<>"",MATCH(DATE($G$18,$H$18,E16),Courses,0)).

This enables any date put into the specific range in sheet2 to autmatically change the color for the correct date cell on the calendar sheet.

I would like to add more than three conditions or categories.

I've tried this code in the calendar sheet with no luck:

Dim rOldSelect As Range 'J.E.McGimpsey 2004-06-12
Dim rOldActivate As Range
Set rOldSelect = Selection
Set rOldActivate = ActiveCell
Range("B:P").Select
Range("B1").Activate
With Selection.FormatConditions
.Delete
.Add _
Type:=xlExpression, _
Formula1:="=IF(B1<>"""",B1<TIME(0,6,10))"
.Item(1).Interior.ColorIndex = 10 'Dark Green
.Add _
Type:=xlExpression, _
Formula1:="=IF(B1<>"""",B1<TIME(0,7,11))"
.Item(2).Interior.ColorIndex = 6 'Yellow
.Add _
Type:=xlExpression, _
Formula1:="=if(B1<>"""",B1<1)"
.Item(3).Interior.ColorIndex = 3 'Red
End With
rOldSelect.Select
rOldActivate.Activate
End Sub

[Bob Phillips]

Can you post the workbook for us to test it with?

[louww]

Here's the file.

The cond. form. is on the first sheet

You'll note there is some other code in the sheet module as well (text boxes displaying input validation messages, example C18).

[Bob Phillips]

Try this

[louww]

Thanks, the three conditions work perfectly.
I've tried to copy the formula statement to add more conditions and named a new range in H3:H32 (activities listed sheet),
and the fourth condition doesn't seem to work

Any ideas?

[Artik]

In versions below 2K7 you can use only three conditions.

Artik

[louww]

Is vba not a way to circumvent the 3 condition limit?

[Artik]

Probably your problem can solve Frank Cable and Bob (http://www.xldynamic.com/source/xld.CFPlus.Download.html)Phillips (http://www.xldynamic.com/source/xld.CFPlus.Download.html).

But I do not give a guarantee :)

Artik

[Bob Phillips]

Probably your problem can solve Frank Cable and Bob (http://www.xldynamic.com/source/xld.CFPlus.Download.html)Phillips (http://www.xldynamic.com/source/xld.CFPlus.Download.html).

But I do not give a guarantee :)

Artik

That is Frank Kabel, not Cable.

louuww, you can also do it by worksheet event code. What is that extra CF?

[Artik]

Kabel, not Cable.
Uuuups, translator remade.
I'm sorry, Frank, if you read this

Artik

[louww]

I've tried the addin you suggested, it doesn't except the match formulas.
The idea is to create a fourth and fifth named range, which acts as additional conditions for formatting dates on the calender.

I've found another piece of code. This replaces a word with a color and the number you specify. This is not what I'm looking for, but at least it can handle more than three conditions:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EndCode
Select Case True
Case InStr(StrConv(Target.Value, vbLowerCase), "oranges") > 0
With Target
'Shade cell - RGB (255, 102, 0) = Orange
.Interior.Color = RGB(255, 102, 0)
'Replace 'oranges' with a space
.Value = Replace(Target.Value, "oranges", "")
'Replace extra space with just a single space
.Value = Trim(Replace(Target.Value, " ", " "))
End With
Case IsEmpty(Target.Value) = True
Target.Interior.ColorIndex = xlNone

End Select

Exit Sub
EndCode:
End Sub

[Bob Phillips]

I've tried the addin you suggested, it doesn't except the match formulas.


I was one of the co-authors of that addin, I can assure you that it handles far more conditions than just match.

[Bob Phillips]

So it what way is it not what you are looking for? That is exactly the type of code I referred to when I said you can do it by worksheet event code.

[louww]

I'm obviously doing something wrong, when I enter the intial match formula (1st post), I get an error message- 'entered condition is not a logic expresion!'.


I'm adding this formula by using' launch CF-Add-formula is' in the addin.

[Bob Phillips]

That means that you have an error in the formula.

[louww]

that formula was copied form the original conditional formatting conditions
It supposed to look at the specific named range in the second sheet and if the date matches the date is colored on the calender sheet.

Any ideas how to solve

[Bob Phillips]

Not without seeing the workbook as you have created it, I am guessing otherwise.

Personally, I would go with simple worksheet event code, modify what you have above.

[louww]

thanks will try to amend or will try something new