Forum Discussion
need help with Excel conditional formatting
I am trying to create a task list where the header row names the individual tasks and the rows below each named column are empty and not formatted until, on any given date listed in the leftmost column, I key an asterisk (*) under a task name for the associated date. I want two things. 1- format the fill color to green for any cell containing an asterisk, which I've achieved with the conditional format rule IF(B2="*",1) format the range of $B$2:$B$35 to green fill. When I enter an Asterisk in B25 That field turns green. I already have this and it works. 2- I also want the header or task name in B1 to format with green fill. How do i get this second parameter to function?
Right now I have 15 task columns, each formatting the field containing the asterisk, but I've had no luck putting green fill for the column heading when I put an asterisk somewhere in that column.
You could simply use the formula =B2="*", no need for IF.
Alternatively use the following rule for B2:P25:
- Select this range.
- Delete the existing rule.
- On the Home tab of the ribbon, select Conditional Formatting > New Rule...
- Select 'Format only cells that contain'.
- Leave the first drop down set to 'Cell Value'.
- Select 'equal to' from the second drop down.
- Enter the formula ="*" in the box next to it.
- Click Format...
- Specify the desired formatting.
- Click OK, then click OK again.
For the header row:
- Select B1:P1.
- B1 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
=COUNTA(B$2:B$25)>0
-
Click Format...
- Apply the desired formatting.
-
Click OK, then click OK again.
You could simply use the formula =B2="*", no need for IF.
Alternatively use the following rule for B2:P25:
- Select this range.
- Delete the existing rule.
- On the Home tab of the ribbon, select Conditional Formatting > New Rule...
- Select 'Format only cells that contain'.
- Leave the first drop down set to 'Cell Value'.
- Select 'equal to' from the second drop down.
- Enter the formula ="*" in the box next to it.
- Click Format...
- Specify the desired formatting.
- Click OK, then click OK again.
For the header row:
- Select B1:P1.
- B1 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
=COUNTA(B$2:B$25)>0
-
Click Format...
- Apply the desired formatting.
-
Click OK, then click OK again.
- perfect! job done and thank you very much!
- Try this formula applied to B1:
=ISNUMBER(MATCH("*",B2:B35,0))
