Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Conditional Formatting 3 conditions
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Conditional Formatting 3 conditions
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 "DeeCee" wrote in message ups.com... | 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.... | | 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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Conditional Formatting 3 conditions
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Conditional Formatting 3 conditions
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! DeeCee wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting for 4 conditions | Excel Worksheet Functions | |||
Conditional Formatting - 2 conditions | Excel Discussion (Misc queries) | |||
2 conditions using conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting should allow more than three conditions. | Excel Discussion (Misc queries) | |||
Conditional Formatting on more than "4" Conditions | Excel Discussion (Misc queries) |