Hello All ...



First off, I'm an okay Excel user ... I can usually get done what I need to and if not, I can look it up somewhere. I don't use macros (although I know how) or VBA. If a project is anything I consider "big," I use Access.

I am an Access power-user ... VBA coding and the whole bit. I came here from AccessForums.net

However, I know some things are better in Excel. I'm at that point with this latest project.

I want to do something, but I don't even know if it's possible ... so here I am.

I am tracking blood chemistry results. I want Excel to fill a cell based on data in another tab. I imagine it's conditional formatting on steroids.

For example:

SDMA Levels ... I have a table on my "Reference" tab that has four SDMA categories ... Normal, Moderate, High, and Severe. Each category has a low value and a high value i.e. Moderate is 13-16. So, the table looks like:

SDMA measured in ug/dl
Low High
Normal 0 14 92.171.8
Moderate 13 16 234.128.12
High 17 20 255.0.0
Severe 21 50 192.0.0


On my test result tab, I have the name of the various things I'm tracking in column "A" and then each column going across has a date at the top and results in the appropriate row.

I'd like Excel to see that on a particular date, the SDMA was 15 and turn the cell orange based on the RGB values in the table above. Obviously, I'd add test results as needed going across ... so the next date, the SDMA is 17 and that cell would turn red based on the RGB value.

I have ten different tests I'm tracking.

I'd also like to know where in the range a specific result falls. I'd like to create a comment for the cell so that when I hover over it, the range appears. If the SDMA was 15, the cell would be orange and the comment would be "Moderate 13-16." I would concatenate the comment from the information in the table.

Is this even possible? I assume I'd need to use VBA, but is there an easier way? I just figured VBA because I'd code it once for each row and be done with it ... I WILL say that I'm hit and miss with using conditional formatting ... sometimes I get the results I want and sometimes, I want to throw a brick at the monitor and just get out my highlighters!

Any help at all would be appreciated!

Susie
Kansas