IF/THEN FORMATTING

[Tom_Tomrell]

Background: I have a column (A) formatted so that it 'fills' a cell with a color based on a numerical range input by me, three ranges used. I have a column (B) that needs a value from columns (C, D, or E) based on the color (value) of (A). Columns C, D, and E contain fixed values to populate (B) based on (A).

Currently I manually adjust column (B). I would like to automate it. I can't seem to find the right function to perform this.

Is there a function/formula that allows (B) to automatically choose (C, D, or E) based on either the FILL or VALUE of (A)? 

[#2]

@Tom_Tomrell 

Let's say the ranges used for column A are:

< 50

>= 50 and < 100

>100

In B2:

=IF(A2<50, C2, IF(A2<100, D2, E2))

Fill down.

[#3]

(1) To get conditional formmatted color need user-defined function.
Function MyColor(rng As Range)
MyColor = rng.Cells.Interior.Color
End Function

(2) Then "SWTICH" VALUE or UserFunction(CELL)
B2 = SWTICH( MyColor(A2), EnumColor1, C, EnumColor2, D, EnumColor3, E)