Highlight all cells of the same category in a calendar dashboard

[samc2696]

Hi all,

I have a calendar in year view that has the dates coloured based off of the type of event it is e.g. meetings are orange, reports due are blue etc etc

At the top, I have a legend/key, and what I want to achieve is that if the user clicks the Legen that is for meetings (orange) then all the cells within the calendar range that are of that category become highlighted (i.e. go to a darker orange or however specified)

Is that possible at all?

To give some background, the calendar is 'populated' using a Events Table on another sheet which has the date, event title and category (amongst other info). When a new event is added, the conditional formatting on the calendar automatically recognises this and based on the event type, fills the correct date's cell. (It is difficult to explain without images or a workbook attached - but it is based on a post by Chandoo found here)

Because of this event type that is assigned to each event, I think there must be a way of linking all the cells that meet that condition with the Legend/Key but I just do not know how!

Thank you
Sam

 

[Johnny Thunder]

Here is a start to your question:

Needs to be enter on the specific sheet that the user will be clicking on the Legend (you can right click on the Tab Name and select View Code to enter this VBA Code)

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim rngCell As Range, Rng As Range, DataSet As Range, Cell As Range


 Set DataSet = Sheet1.Range("A7:I13")
 Set Rng = Sheet1.Range("A1:A3") 'Currently set to Sheet1, you can change to Sheets("Name") if needed
     
     MsgBox Target '-------------Remove this after you have tested
 
 For Each Cell In DataSet
     If Cell.Value = Target Then
         Cell.Interior.Color = RGB(255, 255, 0)
 Else
   
Cell.Interior.Color = xlNone
   
     End If
   
 Next Cell 'Loop Thru dataset
        
End Sub

You will need to write mutliple if Statements to get it to change to the various colors you mentioned.