VBA Macro Xlookup Function Based on Text/Font Color

[CodeSkittle]

Heya I don't know much of anything about VBA or coding or whatnot so please forgive any ignorance I may have but if someone could create a macro for me that would be amazing!
So I have an excel spreadsheet where I keep track of various entertainment I enjoy and in particular shows and any movies for those shows. Along with keeping track of what I watch I have a section on the side to keep track of some 'stats' like how much time I've spent watching or what I've watched the most. Now the thing is with the current function I'm using and how I've ended up making my spreadsheet I can't keep track of my most watched show and movie, only whichever one thing I've watched more. I've done a ton of research and experimenting with functions and other macros I think might help but so far I haven't been able to find or come up with anything. If someone could please help me out here and make a macro for a function that searches for things like xlookup does but it also takes into account the color of the text that would be fantastic!

Currently I'm using this to keep track of what I've watched the most:

=XLOOKUP(MAX(F2:F9),F2:F9,B2:B9)

Here's an extremely watered down snippet of my spreadsheet so you can get a rough idea on how it works:
Green text = Finished/Completely watched show
Blue text = Movie
Grey fill = Finished production
For this function I primarily only care about differentiating the text in column F which will only ever be black/blue if that helps at all.

 

[duggie33]

Hi *CodeSkittle,

I think you need to restructure your data. I am not an expert with data, so I am sure there are others that may have better ideas. My thoughts: have a column to identify it as a Show or Movie, a column for the title, columns for other information about the Show or Movie, a column for if you have finished watching it, your column F data (watch time?), and your column G data (# of episodes?). It might even make sense to have Shows and Movies in separate tables or a separate row for each episode.

With some changes to your structure, you could sort by Show/Movie and by watch times. Conditional formatting might be an alternative. The FILTER function could be useful.

Hope that helps,

Doug

 

[CodeSkittle]

Hi *CodeSkittle,

I think you need to restructure your data. I am not an expert with data, so I am sure there are others that may have better ideas. My thoughts: have a column to identify it as a Show or Movie, a column for the title, columns for other information about the Show or Movie, a column for if you have finished watching it, your column F data (watch time?), and your column G data (# of episodes?). It might even make sense to have Shows and Movies in separate tables or a separate row for each episode.

With some changes to your structure, you could sort by Show/Movie and by watch times. Conditional formatting might be an alternative. The FILTER function could be useful.

Hope that helps,

Doug

Thanks for the suggestion, I'll see what kind of restructuring I could do and I think I did try out the FILTER function before but I'll take another look at it.

Also probably should've said this in my original post but column F is how many times I've watched something and column G is the number of episodes.