Jump to content


Copying "Conditional formatting" in a cell across a row or down a column

Conditional formatting Formulas Copy and Paste

  • Please log in to reply
2 replies to this topic

#1 Runphys



  • Members
  • Pip
  • 2 posts

Posted 28 March 2017 - 02:36 AM

I am trying to have cells in a range have fonts of a certain colour depending on a value that appears in certain cells in another worksheet of that same workbook.

So here’s what I did. I clicked on the first cell of that range (where I want the fonts to have a certain colour) and in “Conditional Formatting” I clicked on “New Rule” followed by “Use a formula to determine which cells to format”. For “Format values where this value is true” I entered =OR(‘Data Input Sheet’X5=”A1”,‘Data Input Sheet’X5=”A2”,’Data Input Sheet’X5=”A3”,’Data Input Sheet’X5=”A4”). For “FORMAT…” I chose the colour I wanted for that rule-red. For the second rule, I had =OR(‘Data Input Sheet’X5=”B1”,‘Data Input Sheet’X5=”B2”,’Data Input Sheet’X5=”B3”,’Data Input Sheet’X5=”B4”) with blue as a colour for that rule, etc.

When I go to copy that format to the next column, X5 does not go to Y5 (as I would like) and when I go to copy that format to the next row, X5 does not go to X6 (as I would like) no matter what I try.

How do I make that possible?

#2 Runphys



  • Members
  • Pip
  • 2 posts

Posted 28 March 2017 - 08:28 PM

Well, it looks like no one had an answer for me but I have finally find out why. You cannot, for conditional formatting, reference a cell from another worksheet or it will not increment the reference as you copy the formula from your first cell to other cells horizontally or vertically.

#3 Jonathan


    Forums Administrator

  • Administrators
  • 807 posts

Posted 29 March 2017 - 12:42 PM

Hi Ruhphys,


You're correct that conditional formatting won't fill down references to cells in another worksheet, but you could work around this by adding a new column that retrieves the data from the second worksheet and then using conditional formatting based on that. You could then hide this column if you didn't want it to be visible.


Cross-worksheet formulas are covered in more depth in the video lesson: Essential Skills Lesson 6-6 Create cross worksheet formulas.

Hiding columns is explained in the video lesson: Expert Skills Lesson 7-7 Hide and unhide worksheets, columns and rows.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users