Hi,
I've googled this question already and can't find an answer that applies, I also have NO experience writing macros in visual basic so hoping there's another way.
I use excel 2007 to keep a record of my gerbil keeping and breeding. ID code, name, gender, age, DoB, genetics/colour, when adopted/rehomed or living here, DoD, and comments on pairings, health etc. I use formulas to work out each animal's age to the day, and an If formula in a hidden column to identify if an animal is deceased, adopted/rehomed, or living here. The results to this formula are NO (animal is deceased), FALSE (adopted/rehomed but living) or YES (living here).
I have already used conditional formatting to highlight the box containing the gender info as Male, Female or Unknown (animal is still in nest).
I want the spreadsheet to highlight the (rest of the) row in one of 3 colours according to the result in the last formula row NO, FALSE or YES. (If necessary I will omit the colour formatting for gender as it is more important for me to quickly identify the animals I have).
I've tried conditionally formatting the whole spreadsheet using a 3-colour scale with a formula =$J4="YES" etc. but the programme tells me I can't use absolute cell referencing in colour scales. J is the column the formula is in, but as it is normally hidden I can move it if required. Row 4 is the first gerbil's data below headers and titles, my 2009 sheet has 26 animals, but I will have to update records back to 2003 once I've sorted it. (Over 100 records!) That's why I tried to do the whole sheet at once, as doing it individually for each record destroys the point of the formula; I'd be quicker to colour it myself. I don't know if I'm writing the formula correctly, or if it would be possible to copy the correct formula down the rows if I'm using conditional formatting rather than a standard formula anyway? ie. J4 J5 J6...
I'm willing to use alternate formulas/methods or rearrange my data as long as the result is that each row is one of three colours as described. I can't write visual basic programming if that's the only way, but I could work out/follow instructions/google how to incorporate it if someone fancies the challenge! Although I keep reading that conditional formatting can be applied when you only have 3 variations, so surely a macro isn't the only way?
Any help gratefully received from those wiser than me! I'm fairly competent on excel, just been a bit thrown by the change from 2003 to 2007. If needed, I can attach the document to clarify.
Thankyou,
Sarah
Bookmarks