V
VancitysFinest
This one might be hard to explain. I'm trying to create a status bar
(mine is shaped like a thermometer and it fills in red) where the cells
fill in using Conditional Formatting.
The user enters 2 numbers, the larger number for the max value of the
thermometer (i.e. 90 degrees F, and a smaller number for the current
status (i.e. 30C). Whatever values the user enters, the values labeling
the thermometer should automatically fit that data, and the cells
beside fill in with a red color.
I currently have it working but I'm looking for a more accurate formula
not using macros that works for a greater variety of numbers. Here is
how it is setup now:
- The cell next to the top of the thermometer (B8) is set to what the
user enters for a max value (i.e. 30)
- The bottom cell (B38) is set to 0.
- The cells inbetween (minor ticks) use this formula:
=B8-($B$8-$B$13)/5 (this is for B9)
- B13 and every 5th cell below it are the major tick marks:
=ROUND(B13-$B$8/6,0)
- the cells to be colored, i.e. D8, are: =IF(B8-1<$G$37,1,0) where G37
is the current value the user entered) so if the value is one
Conditional Formatting sets the color to red, or else it stays white
It works well for max values greater than 10, but below that the status
bar doesn't exactly fill up to where it should. I can send you the file
if you email me if this is unclear! Thanks!
(mine is shaped like a thermometer and it fills in red) where the cells
fill in using Conditional Formatting.
The user enters 2 numbers, the larger number for the max value of the
thermometer (i.e. 90 degrees F, and a smaller number for the current
status (i.e. 30C). Whatever values the user enters, the values labeling
the thermometer should automatically fit that data, and the cells
beside fill in with a red color.
I currently have it working but I'm looking for a more accurate formula
not using macros that works for a greater variety of numbers. Here is
how it is setup now:
- The cell next to the top of the thermometer (B8) is set to what the
user enters for a max value (i.e. 30)
- The bottom cell (B38) is set to 0.
- The cells inbetween (minor ticks) use this formula:
=B8-($B$8-$B$13)/5 (this is for B9)
- B13 and every 5th cell below it are the major tick marks:
=ROUND(B13-$B$8/6,0)
- the cells to be colored, i.e. D8, are: =IF(B8-1<$G$37,1,0) where G37
is the current value the user entered) so if the value is one
Conditional Formatting sets the color to red, or else it stays white
It works well for max values greater than 10, but below that the status
bar doesn't exactly fill up to where it should. I can send you the file
if you email me if this is unclear! Thanks!