Conditional Formatting relating colours to numbers

[aabbey]

Hello.

I have built an input channel list for use in the audio industry. you will see in the second column (Stagebox/Patch) a numbers and a colour. the numbers are not decimal but instead duodecimal.

I have three stage boxes with 12 inputs each and these are represented as
1.1 - 1.12
2.1 - 2.12
3.1 - 3.12
Furthermore the stage boxes are colour coded and this is represented on the sheet also with
Stagebox 1 - Green
Stagebox 2 - Red
Stagebox 3 - Blue

I have completed the sheet as I would like it but I would like the colour filling to be automatic on selection of the number.

There is data validation used on this sheet which limits selections. These can be found hidden on sheet 3.


Kind Regards
Alex Abbey


PS if it's not too much trouble, an explanation of how it was done would be greatly appreciated.

[benishiryo]

so basically 3 different colors based on the first digit?
select the range you want to apply to (say from B2:B24)
go to Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=LEFT(B2)="1"
format color as green

repeat the steps but formula for red and blue to be:
=LEFT(B2)="2"
=LEFT(B2)="3"

LEFT extracts from left to right. it has a 2nd argument which is optional (default is 1). i can actually identify how characters i want to extract. so LEFT(B2,2) would be "1."

[aabbey]

Thanks. problem solved and thanks for the extra info.