• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting-Color whole ROW based on criteria matching other cells

C

Countess

New Member
I am looking to highlight rows where a cell in the row contains a specific combination of words. Here’s an example

A1=Yes

A2=Yellow


Row reads (from A4, B4, C4, etc.):

Row 4: "Yes" text text

Row 5: "Yes, Yellow" text text

Row 6: "No, Yellow" text text

Row 7: "Yes, Yellow" text text

Row 8: "No" text text


So in this example, I want to highlight rows which contain both “Yes” and “Yellow” in cell 1 of the selected rows. Maybe some sort of IF statement like: If$A4:$A8 contains what’s in A1:A2, then CF (whatever’s in the conditional formatting). So I want the formula to look at rows 4 through 8 and see if the text in column 1 matches to both A1 and A2 – in this case “Yes” and “Yellow” and if it does, then it highlights the row, if it doesn’t meet both criteria, then nothing happens. In this case, rows 5 and 7 would be highlighted.

Row 4: Yes text text

Row 5: Yes, Yellow text text (row bolded)

Row 6: No, Yellow text text

Row 7: Yes, Yellow text text (row bolded)

Row 8: No text text


Now I’ve worked out a formula to highlight these rows: =$A4:$A8=”Yes, Yellow”

However I need to find out how to connect it to the content in A1:A2.


Other questions:

How can I limit the cells within the row that are highlighted? Say instead of highlight Row 4, I just want to highlight A4:C4?

On the flip side, how can I use CF if a criteria isn’t matched or “does not contain” or “does not equal”?

So like in my above example, I want rows containing “Yes, Yellow” in the 1st column to be bolded, however rows not containing “Yes, Yellow” should be strikethrough.

Row 4: Yes text text (row strikethrough)

Row 5: Yes, Yellow text text (row bolded)

Row 6: No, Yellow text text (row strikethrough)

Row 7: Yes, Yellow text text (row bolded)

Row 8: No text text (row strikethrough)


!! Better yet, instead of using strikethrough to hide the rows that don’t contain “Yes, Yellow” in the 1st column.

Row 4: Yes, Yellow text text

Row 5: Yes, Yellow text text


I was able to work it out from knowing nothing to this point thanks to this posted: http://chandoo.org/forums/topic/conditional-formatting-color-whole-row-instead-of-cell. Hope someone can help me take it to the next level.


Many thanks!
 
Hi ,


Firstly , you cannot hide / unhide rows using CF ; you will have to use VBA.


For getting the strikethrough and BOLD formats , you can use CF , with the following rules :


=OR(ISERROR(FIND($A$1,A4)),ISERROR(FIND($A$2,A4)))


=NOT(OR(ISERROR(FIND($A$1,A4)),ISERROR(FIND($A$2,A4))))


The first formula is to be used with the strikethrough format , while the second is to be used with the BOLD format.


Before you create these CF rules , select the range where you want these rules to be applied.


Narayan
 
Thanks, looks like this formula may work for me: NOT(OR(ISERROR(FIND($A$1,$A4)),ISERROR(FIND($A$2,$A4)))). However I just needed to at $ before A4 to have the formatting affect the entire row, not just the single cell. Excellent!! It’s even flexible with however I want to format things.


Have another change: In my original sample I was matching 2 criteria and when I tried to expand to 3 (may need to match 6 or 7), the revised formula isn’t working for me: NOT(OR(ISERROR(FIND($A$1,$A4)), ISERROR(FIND($A$2,$A4)),ISERROR(FIND($A$3,$A4)))). Is there a way I can expand it from 2 ISERROR statements to 6-7+?


One more thing. I know I originally wanted to set this up for multiple criteria, but I may be able to streamline it to one. However when I just used: =NOT(OR(ISERROR(FIND($A$1,$A4)))) and A1=No or Yes, it formats any row with No or Yes in it (Row 5-7) vs just formatting rows with only No or only Yes in the cell (row 8 and 4, respectively). Is there a way I can have it highlight the row when it match exactly all the content in the criteria cell, not just contain? (I.e. A1=”Yes, Yellow” then highlight rows where A4:A8 match =”Yes, Yellow” exactly.)


FYI: When I use: =OR(ISERROR(FIND($A$1,$A4)),ISERROR(FIND($A$2,$A4))) and have it search for A1=Yes and A2=Yellow it formats the rows which do not contain Yes or Yellow (rows 6, 8), but it also doesn’t format when it says “yes” only (row 4). Need it to format that line too since it doesn’t contain both Yes and Yellow together. Can that be done?


Many thanks! Almost there.
 
Hi ,


I am somewhat confused by too much descriptive matter ! Can we simplify things a bit ?


1. You have two text strings in A1 and A2 ; it is immaterial what those text strings are , since we will be using the cell references rather than the text strings themselves. So A1 and A2 are the cells we are concerned with , whether they contain YES , YELLOW or any other text.


2. You want to conditionally format a range of cells from row 4 downwards as follows :


If the cell in column A of that row has the text in A1 and the text in A2 , then format the range of cells on that row one way ; if not , format the range of cells on that row another way.


The original formula which was given :


=OR(ISERROR(FIND($A$1,A4)),ISERROR(FIND($A$2,A4)))


will check for whether the text in A1 is present , and the text in A2 is present ; only if both of them are present , then that particular row of the range will be formatted in BOLD ; if only one of them is present , or neither is present , then that particular row of the range will be formatted as strikethrough.


Suppose you enter the text Yes, Yellow ( note the space character between the comma and the letter Y of Yellow ) in A1 , then the following CF formulae will do the same job as the formulae given earlier :


=NOT(ISERROR(FIND($A$1,A4)))


=ISERROR(FIND($A$1,A4))


Narayan
 
Sorry, just trying to be as clear as I can be.


After playing around with your formula I was able to make it work for me. Can't seem to get it to find the exact match, your formula seems to find "containing" vs "exact match" but I think it's best I work with multiple criteria instead of consolidating all the variable into one cell.


So what I wound up using was this:

NOT(ISERROR(FIND($A$1,$A4, NOT(ISERROR(FIND($A$2,$A4, NOT(ISERROR(FIND($A$3,$A4)))))))))

Where the criteria is in A1:A3 and the rows to hightlight start with A4.


Many, many thanks for your help and patience. I'm new to this forum and asking such questions. But I'm glad I did and grateful you took the time to help. :)
 
You must log in or register to reply here.
Back
Top