Forum Discussion
conditional formatting
Hi,
I cant seemed to copy down the formula of conditional formatting that I have to the entire column,
so i want my relative formula to be:
if C1 is "value" and if B1 is empty --> B1 shows red color
if C2 is "value" and C2 is empty --> C1 shows red color
etc
It did work for the first row, C1 B1
but when I copy the formatting to the rest of the column, the reference point still stuck at C1 ,
my formula that I input:
=AND(C1="value", IFBLANK(B1))
what can I do to solve this?
- You need two individual CF rule for two column.
The issue you're encountering is due to the absolute referencing in your conditional formatting formula. In Excel, when you apply conditional formatting and then copy it down the column, Excel by default treats the references as absolute (i.e., they refer to specific cells, like C1 and B1). This is why the formatting appears to be stuck at C1 and B1.
To achieve the desired behavior, you need to ensure that the cell references are relative so that they adjust automatically as you copy the formatting down.
Here’s how you can do it:
- Modify the formula: Change the references in your formula to be relative. This means removing the dollar signs ($) that would otherwise lock the references to specific cells.
Your formula should be:
=AND(C1="value", ISBLANK(B1))
- Apply the conditional formatting:
- Select the range of cells in column B where you want the formatting to apply. For example, if you want to apply it from row 1 to row 100, select B1:B100.
- Go to Home > Conditional Formatting > New Rule.
- Choose "Use a formula to determine which cells to format".
- Enter the formula =AND(C1="value", ISBLANK(B1)).
- Set the formatting (e.g., choose a red fill color).
- Click OK.
- Check the behavior: Excel will now apply the conditional formatting with the formula adjusting for each row automatically (e.g., for row 2, it will use =AND(C2="value", ISBLANK(B2)), and so on).
This should solve the issue, allowing the conditional formatting to adjust correctly as you copy it down the column. The text, steps and functions were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
