VBA & Conditional Formatting >3 conditions

[diane.carney]

Hi

I've created a calendar using a VLOOKUP which shows key meetings and
events. I would like to colour code these, which I have done using
VBA, but I find that unless you physically go into the cell and press
return, the VBA code doesn't change the cell format.

My code is

Set rng = Intersect(Target, Range("weekcal"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
On Error Resume Next
' -- The line above won't change the cell's background
' -- color if the cell's value is not found in the range
' -- that we specified (rngcolors).
cl.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Sheet2").Range("rngColours"), 2,
False)

If Err.Number <> 0 Then
'cl.Interior.ColorIndex = xlNone
cl.Interior.ColorIndex = 2
End If
Next cl
End If

End Sub

Where I have created a range called "rngColours" and placed the
background colour and font colour in.

I have looked through some of the news groups and saw Tom Ogilvy's
reply to Todd Huttenstine using

DIM cell as Range, res as Variant
Worksheets(3).Activate
For each cell in worksheets(3).range("A5:A100")
res = Application.Vlookup(Cell.Value,_
Worksheets(4).Range("M2:Q100"),4,False
etc

I am very new to VBA and would like to know how I could incorporate
this into my code so when a drop down menu is chosen (to choose the
month) and the data changes, the background colour and font will also
change (without having to go into the cell and hit return).

Any help gratefully recieved.

Merry Christmas!

DeeCee

 

[DeeCee]

Sorry my code is

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("weekcal"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
On Error Resume Next
cl.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Data1").Range("rngColour"), 2,
False)
cl.Font.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Data1").Range("rngColour"), 3,
False)
If Err.Number <> 0 Then
'cl.Interior.ColorIndex = xlNone
cl.Interior.ColorIndex = 2
End If
Next cl
End If

End Sub

I clicked on an old version....

 

[Jim Rech]

but I find that unless you physically go into the cell and press return

That's how you coded it:

For Each cl In rng

where "rng" is the cell(s) changed.

If you want all the cells in "weekcal" to reformat you should use:

For Each cl In Range("weekcal")



--
Jim
| Sorry my code is
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| ' Conditional Formatting for more than 3 conditions
|
| Dim rng As Range
|
| Set rng = Intersect(Target, Range("weekcal"))
| If rng Is Nothing Then
| Exit Sub
| Else
| Dim cl As Range
| For Each cl In rng
| On Error Resume Next
| cl.Interior.ColorIndex = _
| Application.WorksheetFunction.VLookup(cl.Value _
| , ThisWorkbook.Sheets("Data1").Range("rngColour"), 2,
| False)
| cl.Font.ColorIndex = _
| Application.WorksheetFunction.VLookup(cl.Value _
| , ThisWorkbook.Sheets("Data1").Range("rngColour"), 3,
| False)
| If Err.Number <> 0 Then
| 'cl.Interior.ColorIndex = xlNone
| cl.Interior.ColorIndex = 2
| End If
| Next cl
| End If
|
| End Sub
|
| I clicked on an old version....
|
| (e-mail address removed) wrote:
| > Hi
| >
| > I've created a calendar using a VLOOKUP which shows key meetings and
| > events. I would like to colour code these, which I have done using
| > VBA, but I find that unless you physically go into the cell and press
| > return, the VBA code doesn't change the cell format.
| >
| > My code is
| >
| > Set rng = Intersect(Target, Range("weekcal"))
| > If rng Is Nothing Then
| > Exit Sub
| > Else
| > Dim cl As Range
| > For Each cl In rng
| > On Error Resume Next
| > ' -- The line above won't change the cell's background
| > ' -- color if the cell's value is not found in the range
| > ' -- that we specified (rngcolors).
| > cl.Interior.ColorIndex = _
| > Application.WorksheetFunction.VLookup(cl.Value _
| > , ThisWorkbook.Sheets("Sheet2").Range("rngColours"), 2,
| > False)
| >
| > If Err.Number <> 0 Then
| > 'cl.Interior.ColorIndex = xlNone
| > cl.Interior.ColorIndex = 2
| > End If
| > Next cl
| > End If
| >
| > End Sub
| >
| > Where I have created a range called "rngColours" and placed the
| > background colour and font colour in.
| >
| > I have looked through some of the news groups and saw Tom Ogilvy's
| > reply to Todd Huttenstine using
| >
| > DIM cell as Range, res as Variant
| > Worksheets(3).Activate
| > For each cell in worksheets(3).range("A5:A100")
| > res = Application.Vlookup(Cell.Value,_
| > Worksheets(4).Range("M2:Q100"),4,False
| > etc
| >
| > I am very new to VBA and would like to know how I could incorporate
| > this into my code so when a drop down menu is chosen (to choose the
| > month) and the data changes, the background colour and font will also
| > change (without having to go into the cell and hit return).
| >
| > Any help gratefully recieved.
| >
| > Merry Christmas!
| >
| > DeeCee
|

 

[DeeCee]

Thanks Jim

I am really new to all this and I generally Google to find code and
then try and fit it to my requirements from there.

I've put your suggested amendment in and it does work for all the range
in "weekcal", but I still have to go into one cell and hit return. Is
there a way of having this done automatically as soon as the validation
list changes?

Many thanks.


Diane

 

[DeeCee]

Hi

I've managed to run a worksheet change macro by calling it via my code
(ie run Worksheet_Change), but it takes ages to execute. Is there a
more efficient way of doing this?

I'm really sorry if this is all very basic stuff for you guys out
there, but I am very new to VBA and try and dip my toe in the water and
am looking at getting some formal training.

Cheers!