0

I have a (probably) simple problem but I just couldn't figure it out.

I have an Excel workbook (Google AdWords report), with just one sheet and simple data in many rows and columns. No formulas, just raw values like numbers, text and percentages.

I wish to color whole row red if a value in that row in a certain column equals something.

For example:

Case 1.
IF value in column C <= 1.5% THEN color row RED; /cell C25 is 1.41% row 25 gets colored red/

Case 2.
IF value in column F begins with text "Below first page..." (theres more but it always begins with this) THEN color row ORANGE

I have absolutely no experience with VBA but do know just a little bit of PHP and CSS so the principles just might come in handy (I hope). This is the reply someone gave here on SA, Coloring complete Row based on values from Columns but I coudn'T get that one to work

Cœur
  • 37,241
  • 25
  • 195
  • 267
Alexander Starbuck
  • 1,139
  • 4
  • 18
  • 31
  • 1
    you may want to research Conditional Formatting in Excel. Your question may be a better fit for [SuperUser](http://superuser.com) as it's not really about programming –  May 08 '14 at 07:09
  • The SO answer you link to in your question is the solution you need - no VBA involved! I agree with mehow that you should post on Superuser if you're having trouble with getting it to work. – aucuparia May 08 '14 at 07:16
  • [Check this out](http://stackoverflow.com/questions/22500483/conditional-formatting-using-3-conditions-in-macro-vb/22501780#22501780) if you want to do it programmatically. – L42 May 08 '14 at 08:05
  • possible duplicate of [Coloring complete row based on values from Columns](http://stackoverflow.com/questions/11280579/coloring-complete-row-based-on-values-from-columns) – Tony Dallimore May 25 '14 at 15:44

1 Answers1

0

This can be done with conditional formatting. Select all the cells you want affected (start selecting from the top of your range, I assume it starts at row 1), then go to

Home tab > Styles group > Conditional Formatting > New Rule > Use formula to determine which cells to format

For Case 1, chose your desired format and set the formula as follows:

=$C1<1.5%

Repeat the process for Case 2 and set the formula as follows:

=LEFT($F1,17)="Below first page…"

Note that when using conditional formatting, a range of cells is selected but there is one corner cell in focus (the white one). When you enter the formula, the conditional formatting is applied to the focus cell using your formula. It is then filled to the remaining cells in your range. Hence, if your range starts on row 2, you need to change $C1 to $C2 and $F1 to $F2.

Calvin
  • 468
  • 3
  • 7
  • Hey Calvin, thanks for saving my life! ;) Just a bit of aditional info - would you please break down this second formula a bit? Left starts at the LEFT cell (or something), F1 is where the search and comparison should start, 17 is the number of characters in the string that is to be searched for? Thanks. – Alexander Starbuck May 08 '14 at 11:19
  • Your welcome! The second formula means take the first 17 characters (the ones on the left) in cell $F1 and check if they are equal to "Below first page…". – Calvin May 08 '14 at 21:09