Conditional Formatting

[Kulset]

Hi,

I'm trying to improve a spreadsheet we use at work, and I have improved it, the problem is that I have to do [lots] of Conditial Formation to get it to work, and I could really need a faster way of doing it.


So here goes.

On row "K" we input a value, when that value is 32 or less the cell turns green, 32 or more it turns red.
What I need, is the two neighbour cells to turn to the same colour as each cell in row "K" is.

So, If K3 is 30, then "I3" & "J3" should turn green aswell, and over 32 or more, it should turn red.

Can anyone help me with this?

Thanks!

[martindwilson]

in k3 two formats
conditon 1
=$K3>32 choose red
conditon 2
=$K3>30 choose green
copy k3
select i3 to k whatever the last row is say k200 then select i3:k200
paste special formats

[daddylonglegs]

Your post does not comply with Rule 6 of our Forum RULES. Common courtesy is the order of the day. Avoid coarse language, provide feedback to suggested solutions, and take the time to thank those who took their time to help you.

[Kulset]

Terrebly sorry for the foul langugae used, I was thoughtless for a moment!

Thanks for the reply "martindwilson".

But I forgott to mention one thing.
The tricky thing actually.

The value which desides if it is "green" or "red" is in cell "H27", so it must be able to be changed from week to week, seeing as this is a KPI (goal for the workplace).
So for every worksheet, cell H27 contains a value (atm 30) and next week we might need to change that to say 32.

I need the color condition to move with the change in cell H27.

Do I need to go into VB coding? Or can this be solved using normal excel?

Thanks!

[martindwilson]

in k3 two formats
conditon 1
=$K3>$h$3 choose red
h3 being the higher adjustable value
conditon 2
=$K3>30 choose green change to
=$K3>$h$4 if you want to adjust the lower value as well h4 being the lower limit
copy k3
select i3 to k whatever the last row is say k200 then select i3:k200
paste special formats
remember cf will work on the first thing it finds true then looks down at each subsequent condition in turn

[Kulset]

Thanks again for your help.

I see where you are going with this, but another problem arises when i use "Paste special"

Here is what goes wrong.

Row "K" is now formated correctly, so if I enter a value equal or less then "H27" it goes green, and red if value is higher.

The type of conditional formating i have used on row "K" is not "formulas" i have used "greater then", "less then or equal to" and "equal to" "0", so it will be white by default.

So when its time to conditional format row "I" and "J", I can't use the "greater then/equal to/less then etc", I have to use "Formulas", seeing as the conditional formating depends of the value in row "K" not in the cells in "I" & "J".

Got a bit complicated, hope this is understandable.
If preferable I can probably upload the spreadsheet so you can have a look on it yourself.

Thanks for all your help so far

[martindwilson]

use formulas throughout
=$K3>$h$3 from k3 copied to j3 or i3 stays the same thats what the $ signs do
copied to j4 and i4 changes to $K4>$h$3

[Kulset]

Fantastic!

I got it to work!!
Thanks alot!

But I have one last question

What formula or condition can i put in the cell to make it be white/not filled when the cell is blank/does not have a number in it?

[martindwilson]

desnt it do that anyway? without seeing what youve put you could use an AND() condition
or you could move them around do the first condition as simply =$k3=""