Jump to content


Photo

Format with Conditional Formatting and Formulas

Using Colors

  • Please log in to reply
5 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 20 July 2017 - 02:26 PM

I have been looking for a means to format rows with color based on data in the preceding row. The WS I've attached has a column that has been highlighted with orange color. I would like to use a formula in Conditional Formatting to color ensuing rows if the constraint is met in the formula.

In the WS I've highlighted in green all rows ensuing  if the value in column AE  >=100. 148, 150, and 181 meet that criteria. Therefore rows 9,12 and 18 have been highlighted in green.

Is there a formula that will allow me to do this and if so can I nest multiple constraints in the formula to choose which rows to highlight?

 

Respectfully,

 

JPK1066

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 20 July 2017 - 06:20 PM

Hi JPK,

 

If you just want to conditionally format based on the value of the cell above being greater than or equal to 100, you can do this with a formula-driven conditional format with this formula: =AE3>=100

 

You can see how formula-driven conditional formats like this work in the video lesson: Essential Skills Lesson 4-18 Create a formula driven conditional format.

 

I have attached a copy of your workbook with the conditional format applied:

 

Attached File  Conditional.xlsx   14KB   21 downloads

Conditional.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this.


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.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 27 July 2017 - 01:41 PM

I used the formula =AB>= 80 for that column and nothing changed.

 

1) I selected all the numbers in that row

2) went to conditional formatting and chose a formula driven format

3) formatted a color after entering the above formula

4) pressed OK

 

this was to high light every cell after any cell that was >=80

 

TY

 

JPK1066



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 27 July 2017 - 06:29 PM

Hi JPK,

 

Formula-driven conditional formats automatically adjust in the same way as other formulas when they are filled down. Therefore, assuming that your data starts in cell AB4, the correct formula will be: =AB3>=80

 

This formula will be automatically adjusted for each cell below the first cell that it is applied to. That means in cell AB5 the formula will be =AB4>=80, in cell AB6 it will be =AB5>=80, and so on: always checking whether the cell above is greater than 80.

 

If this isn't working, it's likely that either the formula or the cells it has been applied to are incorrect. You can check this by using the Conditional Formatting Rules Manager found under Home > Conditional Formatting > Manage Rules.

 

RulesManager.png

 

You can see the rules manager in action in the video lesson: Essential Skills Lesson 4-16 Manage multiple conditional formats using the Rules Manager.


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.


#5 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 27 July 2017 - 10:47 PM

1) In the 4-18 tutorial Mr Smart was able to format the entire row with the insertion of a $ in front of the column, AB in this case, in the formula. I tried that and I got the same result as with no $ sign. Can the entire row be done as in 4-18?

 

2) Can you use a text column in setting a conditional format?   as in =s3,"over"

 

As always, thanks for your time and knowledge,

 

JPK1066



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 31 July 2017 - 09:59 PM

Hi JPK,

 

Yes, you can do the same thing with the formula: =$AB4>=80. The $ sign tells Excel not to adjust the column reference when it applies the conditional format across multiple columns.

 

You can see more about this in the video lesson: Essential Skills Lesson 4-18 Create a formula driven conditional format.

 

You can also use text in conditional formats, yes. To format all cells that contain the word "over", you could use a formula like: =A1="over"


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