How do you conditional format a range of cells based on another range of cells?
Apply conditional formatting to text
- Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
- On the Home tab, click Conditional Formatting.
- Point to Highlight Cells Rules, and then click Text that Contains.
- Type the text that you want to highlight, and then click OK.
How do you highlight cells using conditional formatting?
Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to. On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Text that Contains. In the box next to containing, type the text that you want to highlight, and then click OK.
How to make conditional format cells based on match in another sheet?
Conditional format cells based on match in another sheet 1 If any of the data in Sheet2 matches with Sheet1 (ColumnD) – Make text as blue 2 If there are any duplicate values in sheet2 – Make text as yellow 3 If the above 2 are not there – leave it blank 4 If both Rule 1 and Rule 22 are met – Make text as green More
When to highlight a cell in conditional formatting?
In this example, you’d like to highlight the cells on Sheet1, if they’re higher than the matching cell on Sheet2. The cells on Sheet1 are now highlighted if their value is higher than the matching cell on Sheet2. In earlier versions of Excel, you can’t refer to another sheet in conditional formatting or data validation.
Where do you enter named ranges in conditional formatting?
In conditional formatting, you can enter the reference in the dialog box, but then Excel yells at you. However, you can refer to named ranges anywhere in the workbook, in both conditional formatting and data validation, so you can use that as a workaround.
How to use conditional formatting with data validation?
With data validation, you can’t even click on the other sheet, to try and create a reference. In conditional formatting, you can enter the reference in the dialog box, but then Excel yells at you. However, you can refer to named ranges anywhere in the workbook, in both conditional formatting and data validation, so you can use that as a workaround.