Say i have an excel spreadsheet, and column A contains one day per row for the next however-many years.
How would I go about doing conditional formatting on the rows, so that each month has a different fill colour? (I don't mind if there are repeats, as long as consecutive months don't share a colour.)
My instinct would be to take the month as a number, and use that to formulate the rgb values to use (month*21.25 would spread the number over the colour scale) - but I don't know the way to set the colour that way in excel. I can see how to do a binary (if month%2=0 then highlight, otherwise don't; maybe?) - but not a conditional formatting formula where the colour depends on the value in that way.
Any advice would be appreciated.
You can format all the months that have an odd number by creating a conditional format formula =isodd(month(A1)) (change the A1 to the column where your dates are)
If you want to apply this format to multiple columns, then use a $ to specify an absolute reference for the column containing the date (but not the row): =isodd(month($A1))