SOLVED

Conditional Formatting- Greater than multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-3328640%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting-%20Greater%20than%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3328640%22%20slang%3D%22en-US%22%3Ea%20custom%20formula%20in%20conditional%20formatting%20will%20apply%20the%20formula%20to%20the%20top%20left%20most%20cell%20in%20the%20'Applied%20to'%20range%20and%20then%20move%20to%20each%20other%20cell%20in%20the%20range%20and%20adjust%20the%20formula%20relative%20to%20that%20next%20cell%20location.%20So%20lets%20pretend%20the%20gray%20columns%20are%20A%3AB%20and%20you%20highlight%20columns%20C%3AI%20and%20create%20new%20rule.%20Then%20Enter%3A%3CBR%20%2F%3E%3DISNUMBER(C1)*((C1%26gt%3B%24A1)%2B(C1%26gt%3B%24B1))%3CBR%20%2F%3ESo%20C1%20is%20the%20top%20left%20corner%20of%20the%20range%20so%20If%20C1%20is%20a%20number%20AND%20%26gt%3B%20either%20A1%20or%20B1%20it%20is%20true%3CBR%20%2F%3EThen%20when%20excel%20looks%20at%20D1%20it%20replaces%20each%20C1%20with%20D1%20in%20the%20formula%20but%20the%20%24%20before%20the%20A%20and%20the%20B%20tell%20it%20to%20Lock%20them%20and%20not%20change.%20And%20similar%20when%20it%20goes%20to%20row%202%20all%20the%201s%20become%202s.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3328702%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting-%20Greater%20than%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3328702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1382407%22%20target%3D%22_blank%22%3E%40Manzi69%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20the%20range%20you%20want%20to%20format%20conditionally%20is%20D2%3AJ50%2C%20with%20the%20cells%20in%20gray%20in%20C2%3AC50%3C%2FP%3E%0A%3CP%3ESelect%20D2%3AJ50.%20The%20active%20cell%20in%20the%20selection%20should%20be%20in%20row%202.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20drop-down%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'greater%20than'%20from%20the%20second%20drop-down.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%26nbsp%3B%26nbsp%3B%20%3DMIN(%24B2%3A%24C2)%26nbsp%3B%26nbsp%3B%20in%20the%20box%20next%20to%20it.%20Remember%2C%20B%20and%20C%20are%20the%20columns%20shaded%20gray%2C%20and%202%20is%20the%20row%20number%20of%20the%20active%20cell.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3328410%22%20slang%3D%22en-US%22%3EConditional%20Formatting-%20Greater%20than%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3328410%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20trying%20to%20conditionally%20format%20cells%20that%20are%20greater%20than%20either%20values%20from%202%20other%20cells%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Manzi69_0-1651785843141.png%22%20style%3D%22width%3A%20618px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369590iB9A369B68DE504CB%2Fimage-dimensions%2F618x170%3Fv%3Dv2%22%20width%3D%22618%22%20height%3D%22170%22%20role%3D%22button%22%20title%3D%22Manzi69_0-1651785843141.png%22%20alt%3D%22Manzi69_0-1651785843141.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20to%20colour%20any%20of%20the%20white%20shaded%20cells%20in%20the%20same%20row%20that%20are%20greater%20than%20either%20value%20in%20the%20dark%20grey%20cells.%20As%20you%20can%20see%20some%20cells%20will%20not%20have%20any%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EId%20like%20to%20avoid%20having%20to%20do%20two%20conditional%20format%20rules%20for%20each%20row%2C%20as%20there%20will%20be%20quite%20a%20few%20rows%20so%20if%20there%20is%20a%20way%20to%20use%20one%20formula%20for%20the%20whole%20block%20id%20love%20to%20learn.%3C%2FP%3E%3CP%3E(also%20is%20there%20a%20way%20for%20the%20formula%20to%20ignore%20the%20'%26lt%3B'%20symbol%3F%20those%20cells%20are%20formatted%20as%20numbers%20but%20it%20still%20causes%20an%20error)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20Thank%20you%20both%20for%20the%20help.%20Both%20methods%20work%20great.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3328410%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello,

 

Im trying to conditionally format cells that are greater than either values from 2 other cells:

Manzi69_0-1651785843141.png

I want to colour any of the white shaded cells in the same row that are greater than either value in the dark grey cells. As you can see some cells will not have any values.

 

Id like to avoid having to do two conditional format rules for each row, as there will be quite a few rows so if there is a way to use one formula for the whole block id love to learn.

(also is there a way for the formula to ignore the '<' symbol? those cells are formatted as numbers but it still causes an error)

 

Thanks in advance. 

 

EDIT: Thank you both for the help. Both methods work great.

2 Replies
best response confirmed by Manzi69 (Occasional Visitor)
Solution
a custom formula in conditional formatting will apply the formula to the top left most cell in the 'Applied to' range and then move to each other cell in the range and adjust the formula relative to that next cell location. So lets pretend the gray columns are A:B and you highlight columns C:I and create new rule. Then Enter:
=ISNUMBER(C1)*((C1>$A1)+(C1>$B1))
So C1 is the top left corner of the range so If C1 is a number AND > either A1 or B1 it is true
Then when excel looks at D1 it replaces each C1 with D1 in the formula but the $ before the A and the B tell it to Lock them and not change. And similar when it goes to row 2 all the 1s become 2s.

@Manzi69 

Let's say that the range you want to format conditionally is D2:J50, with the cells in gray in C2:C50

Select D2:J50. The active cell in the selection should be in row 2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'greater than' from the second drop-down.

Enter the formula   =MIN($B2:$C2)   in the box next to it. Remember, B and C are the columns shaded gray, and 2 is the row number of the active cell.

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.