Forum Discussion
Conditional formatting
I want to be able to change the fill of a cell based on the text placed in another cell. Example: If I type "BOILER" into a cell in column H (eg H41) then I want the corresponding cell in another...
Hi smylbugti222gmailcom,
Thank you for your reply. I have managed to get this working, however it's not quite what I am after.
I have a list I am filling in. When I type a word (eg "BOILER" or "NIBE") into a cell on the H column, then I want the same number cell in another column to change colour.
Example 1: I type "BOILER" into H41, then both H41 A41 are highlighted green.
Example 2: I type "NIBE" into H41, then both H41 and A41 are highlighted pink.
I want to apply this rule to the entire column, so going forward, if I type "boiler" into say H234, then both H234 and A234 are highlighted green. If i type "NIBE" then they are highlighted pink, etc.
I thought that the following formula would work but it is not.
=$H:$H "BOILER"
Thank you.
You're on the right track, but the formula needs some adjustments to achieve the desired outcome. Here's how you can modify it:
Revised Formula:
=MATCH($H$2:$H$1000, {"BOILER", "NIBE"}, 0) = ROW()
Explanation:
$H$2:$H$1000: This references the entire range of cells in column H where you want to apply the rule. Adjust the range if your data extends beyond row 1000.
{"BOILER", "NIBE"}: This is an array containing the two text values you want to check for.
MATCH: This function searches for the specified values ("BOILER" or "NIBE") within the provided range and returns the position of the first match. If no match is found, it returns an error (#N/A).
0: This is the match type argument, set to 0 for an exact match.
ROW(): This function returns the current row number.
How it works:
When you enter a text value in a cell within the H2:H1000 range (e.g., "BOILER" in H41), the formula compares it to the values in the array {"BOILER", "NIBE"}.
If there's a match (e.g., "BOILER" matches the first element), the MATCH function returns 1.
The ROW() function also returns the current row number (e.g., 41).
Since both values are equal (1 = 41), the condition is true, and the formatting is applied to both cells in that row (H41 and A41).
If the entered text doesn't match any value in the array (e.g., "NIBE" doesn't match), the MATCH function returns an error (#N/A), and the condition becomes false. Consequently, no formatting is applied.
Applying the Rule:
Select the entire column A where you want the color change to occur.
Follow the steps outlined in the previous response to access Conditional Formatting.
Choose Use a formula to determine which cells to format.
Paste the revised formula in the formula box.
Set the desired formatting for cells that meet the condition (e.g., green fill for "BOILER", pink fill for "NIBE").
Click OK to apply the rule.
Now, whenever you type "BOILER" or "NIBE" in any cell within the H2:H1000 range, the corresponding cells in both columns H and A will change color based on the matching text.
Additional Notes:
You can expand the array in the formula to include more text values for additional color formatting rules.
Adjust the formatting options within the conditional formatting settings to define different colors for each matching text.
This approach ensures that the conditional formatting applies dynamically throughout the entire column based on the text entered in the corresponding cells of column H.
Revised Formula:
=MATCH($H$2:$H$1000, {"BOILER", "NIBE"}, 0) = ROW()
Explanation:
$H$2:$H$1000: This references the entire range of cells in column H where you want to apply the rule. Adjust the range if your data extends beyond row 1000.
{"BOILER", "NIBE"}: This is an array containing the two text values you want to check for.
MATCH: This function searches for the specified values ("BOILER" or "NIBE") within the provided range and returns the position of the first match. If no match is found, it returns an error (#N/A).
0: This is the match type argument, set to 0 for an exact match.
ROW(): This function returns the current row number.
How it works:
When you enter a text value in a cell within the H2:H1000 range (e.g., "BOILER" in H41), the formula compares it to the values in the array {"BOILER", "NIBE"}.
If there's a match (e.g., "BOILER" matches the first element), the MATCH function returns 1.
The ROW() function also returns the current row number (e.g., 41).
Since both values are equal (1 = 41), the condition is true, and the formatting is applied to both cells in that row (H41 and A41).
If the entered text doesn't match any value in the array (e.g., "NIBE" doesn't match), the MATCH function returns an error (#N/A), and the condition becomes false. Consequently, no formatting is applied.
Applying the Rule:
Select the entire column A where you want the color change to occur.
Follow the steps outlined in the previous response to access Conditional Formatting.
Choose Use a formula to determine which cells to format.
Paste the revised formula in the formula box.
Set the desired formatting for cells that meet the condition (e.g., green fill for "BOILER", pink fill for "NIBE").
Click OK to apply the rule.
Now, whenever you type "BOILER" or "NIBE" in any cell within the H2:H1000 range, the corresponding cells in both columns H and A will change color based on the matching text.
Additional Notes:
You can expand the array in the formula to include more text values for additional color formatting rules.
Adjust the formatting options within the conditional formatting settings to define different colors for each matching text.
This approach ensures that the conditional formatting applies dynamically throughout the entire column based on the text entered in the corresponding cells of column H.