Excel Extending Conditional Formatting to another column

Question

I have Two columns Bytes (B$) and Human Readable (C$). Bytes will be a list of integers and have conditional formatting 'Graded Color Scale' 3-color scale: Green Lowest Value, Yellow 50%, and Red Highest Value.

Human Readable is a formula WHERE B is the Bytes Column.

=
IF(B2>(2^60),TEXT(B2/(2^60),"0.0")&" EB",
IF(B2>(2^50),TEXT(B2/(2^50),"0.0")&" PB",
IF(B2>(2^40),TEXT(B2/(2^40),"0.0")&" TB",
IF(B2>(2^30),TEXT(B2/(2^30),"0.0")&" GB",
IF(B2>(2^20),TEXT(B2/(2^20),"0.0")&" MB",
IF(B2>(2^10),TEXT(B2/(2^10),"0.0")&" KB",
TEXT(B2,"0.0")&" KB"))))))

I would like to extend the color from the conditional formatting on Column B to the corresponding Cell in Column C.

Apply the formatting to Column C using the same criteria as column B, refer to column B not C as the criteria. — Scott Craner
Under the conditional formatting section I can't find a source section on an applies to section. Graded color scale, format three color, Applies to "=$B:$B" when I tried =$B:$C" nothing happened at all. B stayed colored c stayed white. — shaun
You are correct, can't be done with gradient. gradient looks at the Applies To: Range. Which you can change on the Manage Rules but will not work in this case. — Scott Craner

Unknown Unknown · Accepted Answer · 2018-04-22T12:20:00

Extending the range of a 'Graded Color Scale` includes further values in the scale. If you want to replicate formatting from the original range over values in an extended range you may require something like VBA (many examples on SO already).

can't be done with gradient. gradient looks at the Applies To: Range. Which you can change on the Manage Rules but will not work in this case. – Scott Craner Jul 18 '17 at 17:18

Excel Extending Conditional Formatting to another column

1 Answers