How to colour box based on value

[jamshoot]

Hi there

Need help from all excel guru out there.
Have done some googling but nothing show up what I want.

What I need.
The formula is similar to REPT.
But this formula can only create the number of symbol based on value.

What I want is to colour the number of cell on the right based on value.

Cheer

 

[JonXL]

Hi there

Need help from all excel guru out there.
Have done some googling but nothing show up what I want.

What I need.
The formula is similar to REPT.
But this formula can only create the number of symbol based on value.

What I want is to colour the number of cell on the right based on value.

Cheer

What cell do you want colored, what cell has the value in it, and what is the condition for which you want the former colored?

 

[jamshoot]

What cell do you want colored, what cell has the value in it, and what is the condition for which you want the former colored?

Hi there

Thanks for replying

Let say my data is at

A1 = 5 I want a formula to format B1 to F1 to colour the cell
A2 = 3 I want a formula to format B2 to E2 to colour the cell
A3 = 2 I want a formula to format B3 to C3 to colour the cell
A4 = 1 I want a formula to format cell B4 to colour the cell

Cheers

 

[Smudger_101]

Hi there

Thanks for replying

Let say my data is at

A1 = 5 I want a formula to format B1 to F1 to colour the cell
A2 = 3 I want a formula to format B2 to E2 to colour the cell
A3 = 2 I want a formula to format B3 to C3 to colour the cell
A4 = 1 I want a formula to format cell B4 to colour the cell

Cheers

Conditional formatting is your friend here.

1. Select the cells that you want to colour (B1:F1);
2. Select 'Conditional Formatting';
3. Select 'New Rule';
4. Select 'Use a formula to determine which cells to format';
5. Then select the icon in the edit rule description window;
6. When the new formatting rule window appears click on the cell you wish to check (=$A$1 will appear). To this add ‘=5’, then click on the icon at the end;
7. When the New Formatting Rule window is displayed, simply click on ‘Format…’ and select your colour scheme. Then just click on OK, Apply, etc, to finish the process.

Hope that helps...

 

[jamshoot]

Hi

Thanks for the quick reply.

Noted your solution.
Is there another way or macro to do it auto instead of manually.
I would prefer if there is a value input in A1, it will auto make B1 to F1 coloured.
Just like the formula REPT
=REPT("I",A1)
But this formula can generate 5 "I" in one cell in B1.
But what I want is to colour B1 to F1 automatically.

Cheers

 

[Smudger_101]

Hi

Thanks for the quick reply.

Noted your solution.
Is there another way or macro to do it auto instead of manually.
I would prefer if there is a value input in A1, it will auto make B1 to F1 coloured.
Just like the formula REPT
=REPT("I",A1)
But this formula can generate 5 "I" in one cell in B1.
But what I want is to colour B1 to F1 automatically.

Cheers

Setting up the conditional formatting will make it automatic.

If cell A1 is blank, or a value other than 5 (or whatever you set it to), the cells will remain un-coloured. If you enter 5, then the cells will colour automatically.

 

[jamshoot]

Setting up the conditional formatting will make it automatic.

If cell A1 is blank, or a value other than 5 (or whatever you set it to), the cells will remain un-coloured. If you enter 5, then the cells will colour automatically.

Hi

Thanks again for the advice.
Have try out but that is only when I enter A1=5.
What I want is, if I enter other value, excel can take the value in A1 & colour accordingly.
eg:-
if A1 = 1... then one cell is coloured
if A1 = 4... then 4 cell is coloured.

Cheers

 

[Smudger_101]

Hi

Thanks again for the advice.
Have try out but that is only when I enter A1=5.
What I want is, if I enter other value, excel can take the value in A1 & colour accordingly.
eg:-
if A1 = 1... then one cell is coloured
if A1 = 4... then 4 cell is coloured.

Cheers

I would be tempted to set the conditional formatting up as I mentioned previously; but then set up additional rules in the rules manager. So they would be:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Rule (applied in the order shown)[/TD]
[TD]Format[/TD]
[TD]Applies to[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=1[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=2[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$C$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=3[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$D$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=4[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$E$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=5[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$F$1[/TD]
[/TR]
</tbody>[/TABLE]

Repeat as needed for A2, A3, A4, etc.

Hope that helps...

 

[jamshoot]

I would be tempted to set the conditional formatting up as I mentioned previously; but then set up additional rules in the rules manager. So they would be:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Rule (applied in the order shown)[/TD]
[TD]Format[/TD]
[TD]Applies to[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=1[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=2[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$C$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=3[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$D$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=4[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$E$1[/TD]
[/TR]
[TR]
[TD]Formula: =$A$1=5[/TD]
[TD]AaBbCcYyZz[/TD]
[TD]=$B$1:$F$1[/TD]
[/TR]
</tbody>[/TABLE]

Repeat as needed for A2, A3, A4, etc.

Hope that helps...

Hi there

thanks for the help.
Yes.. That's a great idea. I never thought of it. It works.
Thanks for your time and tips.

cheers.

 

[Smudger_101]

Hi there

thanks for the help.
Yes.. That's a great idea. I never thought of it. It works.
Thanks for your time and tips.

cheers.

No problem - glad it works okay.