I have to be able to format specific cells with a background colour if they
match a particular condition. I can use Conditional Formatting, but I need
more than 3 format options. Below are the formulae I am want to use
=AND(O$3=$C5,O$3<=$D5) green
=AND(O$3=$F5,O$3<=$G5) grey
=AND(O$3=$I5,O$3<=$J5) blue
=AND(O$3=$L5,O$3<=$M5) yellow

So for example O5 should be green if condition =AND(O$3=$C5,O$3<=$D5) is met.

The outcome is to help with creating a sort of a gant chart. I am sure this
could be done with VB, but I am only just starting out with it. Any pointers
or help with the code would be gratefully received.

Here is a simple example.

Adapting it to your coide is not obvious to me as I don't which cells in
your formule are being CFed.


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"sot" wrote in message
...
I have to be able to format specific cells with a background colour if they
match a particular condition. I can use Conditional Formatting, but I
need
more than 3 format options. Below are the formulae I am want to use
=AND(O$3=$C5,O$3<=$D5) green
=AND(O$3=$F5,O$3<=$G5) grey
=AND(O$3=$I5,O$3<=$J5) blue
=AND(O$3=$L5,O$3<=$M5) yellow

So for example O5 should be green if condition =AND(O$3=$C5,O$3<=$D5) is
met.

The outcome is to help with creating a sort of a gant chart. I am sure
this
could be done with VB, but I am only just starting out with it. Any
pointers
or help with the code would be gratefully received.

I used used under tools macro Learn New macro and got this code
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/19/2007 by Joel Warburg
'

'
Range("A1").Select
Selection.Interior.ColorIndex = 4
Range("A2").Select
Selection.Interior.ColorIndex = 15
Range("A3").Select
Selection.Interior.ColorIndex = 8
Range("A4").Select
Selection.Interior.ColorIndex = 6
Range("I8").Select
End Sub

Now the trick is to modify the code for you application

Sub Macro1()
if (range("O3").value = range("C5")) and (range("O3").value =
range("D5")) then
Range("A1").Select
Selection.Interior.ColorIndex = 4
end if
if (range("O3").value = range("F5")) and (range("O3").value =
range("G5")) then
Range("A1").Select
Selection.Interior.ColorIndex = 15
end if
if (range("O3").value = range("I5")) and (range("O3").value =
range("J5")) then
Range("A1").Select
Selection.Interior.ColorIndex = 8
end if
if (range("O3").value = range("L5")) and (range("O3").value =
range("M5")) then
Range("A1").Select
Selection.Interior.ColorIndex = 6
end if
end sub





"sot" wrote:

I have to be able to format specific cells with a background colour if they
match a particular condition. I can use Conditional Formatting, but I need
more than 3 format options. Below are the formulae I am want to use
=AND(O$3=$C5,O$3<=$D5) green
=AND(O$3=$F5,O$3<=$G5) grey
=AND(O$3=$I5,O$3<=$J5) blue
=AND(O$3=$L5,O$3<=$M5) yellow

So for example O5 should be green if condition =AND(O$3=$C5,O$3<=$D5) is met.

The outcome is to help with creating a sort of a gant chart. I am sure this
could be done with VB, but I am only just starting out with it. Any pointers
or help with the code would be gratefully received.

right click on the sheet tab and select view code. Put in code like this

Private Sub Worksheet_Calculate()
Dim i as Long, j as Long, jj as Long
Dim bMatch as Boolean
bMatch = False
j = 0
for i = 3 to 14 step 3
' i refer to column C, F, I, L sequentially
j = j + 1
if Range("O3") = cells(5,i) and _
Range("O3") <= cells(5,i+1) then
bMatch = true
jj = j
exit for
end if
Next
if bMatch then
with Range("O5")
Select Case j
Case 1: .Interior.ColorIndex = 4 'green
Case 2: .Interior.ColorIndex = 15 'gray
Case 3: .Interior.ColorIndex = 41 'blue
Case 4: .Interior.ColorIndex = 6 'Yellow
End Select
End With
else
Range("O5").Interior.ColorIndex = xlNone
End if
End Sub

this will fire whenever there is a calculate event - just like conditional
formatting.

--
Regards,
Tom Ogilvy

"sot" wrote:

I have to be able to format specific cells with a background colour if they
match a particular condition. I can use Conditional Formatting, but I need
more than 3 format options. Below are the formulae I am want to use
=AND(O$3=$C5,O$3<=$D5) green
=AND(O$3=$F5,O$3<=$G5) grey
=AND(O$3=$I5,O$3<=$J5) blue
=AND(O$3=$L5,O$3<=$M5) yellow

So for example O5 should be green if condition =AND(O$3=$C5,O$3<=$D5) is met.

The outcome is to help with creating a sort of a gant chart. I am sure this
could be done with VB, but I am only just starting out with it. Any pointers
or help with the code would be gratefully received.