Forum Discussion
IF/THEN FORMATTING
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 Replies
- (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) 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.
