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...
Here's how you can achieve conditional formatting based on the text in another cell in Excel:
Steps:
Select the target cell where you want the color change to occur: In your example, select cell A41 (or the corresponding cell for other rows).
Go to the "Conditional Formatting" menu:
- Click on the "Conditional Formatting" button in the "Home" tab.
- Alternatively, right-click on the selected cell and choose "Format Cells..." from the context menu. In the "Format Cells" pane, go to the "Fill" tab and click on "Conditional formatting..."
Set up the formatting rule:
- In the "Conditional Formatting Rules Manager" window, choose "New Rule..."
- Select "Use a formula to determine which cells to format" from the options.
Enter the formula:
- In the "Format values where this formula is true" field, enter the following formula:
Excel=H41="BOILER" // Replace H41 with the cell reference containing the text you want to checkThis formula checks if the value in cell H41 (or the specified cell) is equal to "BOILER" (replace "BOILER" with the desired text). If true, the formatting will be applied.
Set the formatting:
- Click on the "Format..." button to choose the fill color you want to apply when the condition is met.
- Click "OK" to close the "Format Cells" window.
Explanation:
- The formula =H41="BOILER" compares the value in cell H41 with the text "BOILER". If they match, the formula evaluates to TRUE, and the formatting is applied.
- You can adjust the cell reference in the formula (e.g., =I42) to match the cell containing the text you want to check for in each row.
- You can also modify the formatting options (e.g., font color, border style) as desired.
Additional Notes:
- This approach applies the formatting only to the selected cell. To apply the rule to multiple cells, select the entire range where you want the conditional formatting to be applied before following the steps above.
- You can create multiple conditional formatting rules with different formulas and formatting options to handle various scenarios based on the text in the other cell.
By following these steps, you can effectively change the fill color of a cell based on the text content in another cell, enhancing the visual clarity and organization of your spreadsheet.
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. Select column A, then hold down Ctrl while you select column H. H1 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$H1="BOILER"
The $ before the column letter H is essential.
Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.Repeat these steps, but with
=$H1="NIBE"
and pink as color.
Repeat as needed for other values.
- Hi Hans,
Thank you for your reply. I tried this method but it did not work for me.
Regards.Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
