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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies