Home

Conditional Formatting to give specific reference

%3CLINGO-SUB%20id%3D%22lingo-sub-657748%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20to%20give%20specific%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657748%22%20slang%3D%22en-US%22%3E%3CP%3EA1%20contains%20a%20value%2C%20B1%20has%20conditional%20formatting%20that%20shades%20it%20amber%20if%20A1%20is%20greater%20than%205%20and%20red%20A1%20is%20greater%20than%2010.%20I%20can%20duplicate%20this%20in%20B2%20by%20format%20painting%20so%20that%20it%20relates%20to%20value%20in%20B1%20and%20can%20carry%20on%20doing%20this%20one%20row%20at%20time.%3C%2FP%3E%3CP%3EI%20have%20about%203000%20rows%20to%20apply%20this%20to.%20If%20I%20use%20copy%20and%20paste%20or%20if%20I%20use%20format%20painter%20for%20a%20block%20of%20cells%20in%20column%20B%20the%20result%20always%20gives%20me%20a%20range%20in%20which%20the%20formula%20is%20anchored%20to%20the%20cell%20in%20column%26nbsp%3BA%20from%20the%20point%20of%20copying%20(despite%20the%20original%20not%20being%20identified%20with%20the%20%24%20sign)%20and%20the%20range%20to%20which%20it%20applies%20is%20given%20as%20the%20whole%20range%20I%20just%20copied%20to%20rather%20than%20that%20specific%20row.%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20I%20can%20speedily%20replicate%20the%20formula%20in%20B1%20across%20a%20range%20of%20rows%20so%20that%20for%20example%20B1000%20would%20be%20exactly%20the%20same%20as%20B1%20except%20in%20the%20row%20reference%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-657748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659306%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20to%20give%20specific%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351784%22%20target%3D%22_blank%22%3E%40Farngorn%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3ESelect%20you%20range%20or%20entire%20column%20B%2C%20select%20the%20rule%20which%20%22Use%20a%20formula...%22%2C%20use%20formula%20%3D%24A1%26gt%3B10%20(for%20red)%2C%20apply%20desired%20format%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20513px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116646iD56CF348009F7758%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20apply%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20581px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116647iFF65DD12400ED57A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESame%20for%20another%20rule%3C%2FP%3E%3C%2FLINGO-BODY%3E
Farngorn
Occasional Visitor

A1 contains a value, B1 has conditional formatting that shades it amber if A1 is greater than 5 and red A1 is greater than 10. I can duplicate this in B2 by format painting so that it relates to value in B1 and can carry on doing this one row at time.

I have about 3000 rows to apply this to. If I use copy and paste or if I use format painter for a block of cells in column B the result always gives me a range in which the formula is anchored to the cell in column A from the point of copying (despite the original not being identified with the $ sign) and the range to which it applies is given as the whole range I just copied to rather than that specific row.

Is there any way I can speedily replicate the formula in B1 across a range of rows so that for example B1000 would be exactly the same as B1 except in the row reference?

1 Reply

@Farngorn ,

Select you range or entire column B, select the rule which "Use a formula...", use formula =$A1>10 (for red), apply desired format

image.png

and apply

image.png

Same for another rule