+ Reply to Thread
Results 1 to 3 of 3

Copying Conditional Formatting Border Color Using Absolute Referenced Merged Cells

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    Indiana
    MS-Off Ver
    Office 2013
    Posts
    40

    Copying Conditional Formatting Border Color Using Absolute Referenced Merged Cells

    So I have a conditional formatting rule that generates a pink fill & red border around a group of merged cells. Because they are merged, I have to use absolute cell referencing to get the border to show around the whole range of merged cells. The conditional formatting is applied based on data input into the cell immediately above it in the column (which is also a range of merged cells).

    I have 100 other merged cells I need to copy this formatting rule to, but the rule for each of these cells needs to reference the cell directly above it to determine if the formatting needs to be applied. I have found I can successfully do this, but NOT when I'm using absolute cell referencing in the original cell I'm copying the rule from.

    So for instance, the cell fill color will show as pink, and the border as red, but the border won't wrap all the way around the merged cells. If I try and copy a conditional format rule that uses absolute cell referencing, the rule applied to the pasted cells will only reference cell locations from the original rule in the original cell.

    Thus, the only way I see getting this done is to copy the conditional formatting rule (without absolute cell reference), and then go in and manually change each of the 100 other cell's rule formula to add "$" where applicable. I want to avoid this by any means necessary.

    Any way to get this done?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    What cells are you merging?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-08-2012
    Location
    Indiana
    MS-Off Ver
    Office 2013
    Posts
    40

    Re: Copying Conditional Formatting Border Color Using Absolute Referenced Merged Cells

    Here is my conditional formatting rule:

    =AND($J$14>0,$J$18="")

    J18 is the cell the rule is being applied to. If that cell is empty, and J14 (the cell immediately above it) has a date in it, the formatting will engage.

    Cell J18 is a merged range of $J$18:$J$21

    Cell J14 is a merged range of $J$14:$J$17

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1