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.
matching a string on a row in a column and coloring the cells if a match occurs
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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"
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
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
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
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
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
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
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
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????
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.
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.
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
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
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.
Please have a look at the attached and let me know if this is working as desired now.
Conditional_formating.xlsx
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"
Please have a look at the attached and let me know if this is working as desired now.
Conditional_formating.xlsx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial