SOLVED
Home

Conditional Formatting values across tables

%3CLINGO-SUB%20id%3D%22lingo-sub-290863%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20values%20across%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290863%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20someone%20please%20assist%20me%20with%20this.%20I%20have%20attached%20a%20photo%20to%20add%20context.%3C%2FP%3E%3CP%3EWhen%20I%20write%20values%20into%20the%20green%20table%20I%20would%20like%20the%20figures%20to%20become%20red%20if%20they%20are%20less%20then%20the%20corresponding%20cell%20in%20the%20yellow%20table.%20I%20know%20how%20to%20format%20each%20cells%20independently%20but%20is%20there%20a%20way%20to%20do%20it%20multiple%20times%3F%20It%20won't%20work%20if%20highlight%20multiple%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60615iAC4FD437D9D484BF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202018-11-25%20at%208.21.13%20pm.png%22%20title%3D%22Screen%20Shot%202018-11-25%20at%208.21.13%20pm.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-290863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291046%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20values%20across%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291046%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20yellow%3C%2FP%3E%0A%3CPRE%3E%3D(E3%26lt%3BA3)*(E3%26gt%3B%3D0.9*A3)%3C%2FPRE%3E%0A%3CP%3Eand%20red%3C%2FP%3E%0A%3CPRE%3E%3D(E3%26lt%3B0.9*A3)*NOT(ISBLANK(E3))%3C%2FPRE%3E%0A%3CP%3Eif%20I%20understood%20you%20correctly%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290969%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20values%20across%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290969%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3Ewhat%20would%20the%202%20formulas%20be%2C%20if%20the%20value%20was%20%26lt%3B%20A3%20but%20only%20within%2010%25%20to%20equal%20yellow%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20if%20it%20was%20greater%20then%20%26lt%3B10%25%20%3D%20red%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290947%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20values%20across%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290947%22%20slang%3D%22en-US%22%3E%3CP%3EWorks%20Perfectly%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANKYOU!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290901%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20values%20across%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290901%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20suggest%20the%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3D(E3%26lt%3BA3)*NOT(ISBLANK(E3))%3C%2FPRE%3E%0A%3CP%3Eapplied%20to%20entire%20range.%20With%20that%20you%20don't%20format%20blank%20cells%20and%20do%20not%20generate%20a%20bunch%20of%20rules%20(one%20rule%20for%20each%20cell)%20as%20with%20Format%20painter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290869%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20values%20across%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290869%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Eyou%20can%20do%20it%20column%20by%20column%20but%20you%20will%20still%20need%20to%20do%20the%20first%20cell%20manually%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20use%20conditional%20formatting%20for%20the%20first%20cell%26nbsp%3B%20then%26nbsp%3B%20use%20Format%20Painter%20and%20extend%20to%20the%20end%20of%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eunfortunately%3A%20the%20empty%20cells%20will%20considered%20have%20the%20value%20(%200)%20so%20they%20will%20be%20painted%20in%20RED%20till%20you%20fill%20them%20with%20correct%20numbers%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60617i6D371297A7A41CC0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
ChrisJP
New Contributor

Hi, 

Could someone please assist me with this. I have attached a photo to add context.

When I write values into the green table I would like the figures to become red if they are less then the corresponding cell in the yellow table. I know how to format each cells independently but is there a way to do it multiple times? It won't work if highlight multiple cells.

 

Cheers, 

Chris

 

Screen Shot 2018-11-25 at 8.21.13 pm.png

 

 

 

5 Replies
Highlighted

Hi,

you can do it column by column but you will still need to do the first cell manually 

 

- use conditional formatting for the first cell  then  use Format Painter and extend to the end of the column.

 

unfortunately: the empty cells will considered have the value ( 0) so they will be painted in RED till you fill them with correct numbersCapture.PNG

Highlighted
Solution

I'd suggest the rule with formula

=(E3<A3)*NOT(ISBLANK(E3))

applied to entire range. With that you don't format blank cells and do not generate a bunch of rules (one rule for each cell) as with Format painter.

 

Highlighted

Works Perfectly,

 

THANKYOU!

Highlighted
Hi,
what would the 2 formulas be, if the value was < A3 but only within 10% to equal yellow

and if it was greater then <10% = red
Highlighted

Hi,

 

When yellow

=(E3<A3)*(E3>=0.9*A3)

and red

=(E3<0.9*A3)*NOT(ISBLANK(E3))

if I understood you correctly

 

Related Conversations
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
Filter complex table to simple table
Sam2009 in Excel on
1 Replies
Pivot table
gabriellerocha in Excel on
5 Replies
Excel Table Size in Macro
WayneEK in Excel on
6 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies