• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formating in cells that contain two values from two other cells

G

giorgiokatr

New Member
Hi there i have a series of cells that each contains two values from two other cells

Α Β C
1 141 11 141 (11%)
2 144 13 144 (13%)

i use the function =A1&"("&B1&")"


is there any way to apply conditional formating to cells C1 and C2 based on the percentage value?
 
Hi Giorgio .

Since the percentage values are just taken from column B , we can certainly apply conditional formatting to the cells in column C based on the values in the corresponding cells in column B ; you need to specify the kind of CF that you want applied.

Narayan
 
thanks narayank991!
awesome!
only a question
why did you set
=B1=MAX($B$1:$B$2)

and not =MAX($B$1:$B$2)

why do you have B1 as a benchmark?

also unfortunately it does not work if values $B$1:$B$2 are in another sheet!
 
Last edited:
Hi Giorgio ,

What we are telling Excel is that if the cell to the left of the current cell in column C is having the maximum value within column B , then color the current cell in column C.

Since we have used B1 , and not B$1 , as we go down , Excel internally changes the CF formula to :

=B2 = MAX( $B$1:$B$2 )

even though the CF formula will not change , since it has been applied to the range C1:C2 ; only if you copy the format from one cell to the other does the CF formula change.

Thus , if you had initially applied the rule to only cell C1 , and then copied the formatting rule to C2 , you would see the CF formula for C2 as :

=B2 = MAX( $B$1:$B$2 )

---------------------------------------------------------------------------------------------------------------

As far as your second question is concerned , CF rules do not work with other sheet references , but the way around this is to use defined names or named ranges ; these can then be applied to CF rules.

See your file now.

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top