matching a string on a row in a column and coloring the cells if a match occurs

Patricia Timm
Patricia Timm used Ask the Experts™
on
I would like to check a column called Part Rcvd if the row in the column matches Rcvd (this will be an exact match as I hope it will be hard coded in by the end user) then color the matching cell and adjacent cells in column N and column O to grey.
As an example
column N                       column O                         column P
12/3/2019                       12/10/2019                       Rcvd
Finds match in column P for string Rcvd then greys out column N, column O, and column P . Loops thru for each row within that column until it reaches the end. Thanks in advance. Would it be better to code this in VB or use a formula manager in excel to create? Not sure which is more efficent. There currently are no formulas or vb code running on this sheet. More informational so process time is short
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You can easily achieve this with the help of Conditional Formatting.
Assuming your data starts from Row2 then select the range N2:P5000 and make a new rule for Conditional Formatting using the formula given below and set the format as per your choice e.g. fill with light grey color and font color with dark grey color.

To select the range fast, you can type N2:P5000 in the Name Box (where you see the cell address of the selected cell) and hit enter and the range N2:P5000 will be selected and then you can create a new rule for conditional formatting for the selected range. You may select the range as per your requirement.

Formula:
=$P2="Rcvd"

Open in new window

If your data starts from a different row, say row10, the above formula should be changed to =$P10="Rcvd" as the row reference used in the formula should be the first row in the selected range.

Author

Commented:
Thanks I think I am almost there
I go to conditional formatting and new rule and use a formula to determine which cells to format
then I see format values where this formula is true and I enter in the criteria but I dont see a place to choose the range to check . I need to start checking P17 to the bottom of the column as their are new entries. Loop thru the entire column and if there is a match in column P called "Rcvd" then color the columns N,O, and P within that row grey. Trying to figure out where to identify the range to color code. Thanks in advance

Author

Commented:
Figured out select range first then i created the new rule. In my conditional formating rules manager I see my formula
Included the formula as an attachment. I made sure that in P19 the string "Rcvd" resides. The formula when applied does nothing. Not sure what I am doing wrong
formula.docx
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
When you apply the custom formatting to the whole column, the active cell is always the first cell in the first row in the selected column.
i.e. your conditional formula is applied to the range $N:$P so the active cell is N1 whereas your formula is referring to the cell P19 therefore it will not work correctly.

To do this properly, select the whole columns N:P and make a new rule using the formula given below and set the format as per your choice.

=$P1="Rcvd"

Watch this demo video in which I have applied the conditional formatting rule.
ConditionalFormattingRcvd.mp4

Author

Commented:
Great illustration... I followed steps exactly but the cells did not change color based on the conditional statement provided above. Do I need to enact in some way the formula??? Not sure what I am doing wrong. I verified that I can edit the spreadsheet. I am playing around with to try and make it work. Thanks in advance I really appreciate your help

Author

Commented:
The cells are already highlighted grey as someone manually changed them already. I am trying to make them yellow now for testing purposes to see if formula works. Maybe I have to clear the formatting first????

Author

Commented:
I would'nt think that would make a difference
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
To change the formatting, first select a cell with the conditional formatting and then click on Conditional Formatting on Home Tab and choose Manage Rules and then in the next window, click on Edit and click on Format button to reset the formatting.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may send me the file so that I can implement it for you. If your file has any sensitive info, please send me the file through the private message.

Author

Commented:
I have included part of the file. These are the steps I took and still no results
I would like to learn my mistake.
1. I chose column e, f, and g via your demo
2. I went into conditional formating new rule
3. Use a formula to determine which cells to format
4. typed in =$G1="Rcvd"
5. chose format and a color yellow - did not want to change the font so left that alone - just the cell colored yellow
6. clicked ok and ok and nothing happened
Thanks for reviewing where my error is occuring - appreciate your help
Conditional_formating.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You did everything correct but if you look at the cell G4, it contains Rcvd but it also contains an extra space in the end and as a result the formula =$G1="Rcvd" returns false for the row4.

I deleted extra rules from the sheet and edited the existing rule and changed the formula for conditional formatting as below and everything worked as expected.

=TRIM($G1)="Rcvd"

Open in new window


Please have a look at the attached and let me know if this is working as desired now.
Conditional_formating.xlsx

Author

Commented:
Thanks Subodh!
I should have thought that there might have been a space added by the end user.

Author

Commented:
Whops when I tried on the actual sheet it highlighted all the cells even those that did not have "Rcvd"

Author

Commented:
Can I send you the actual sheet??? thanks
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Okay, attach the file in a private message.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial