Billkronmiller
asked on
recognize color fromat
I have a cell I want to show the answer of 1 or 0. If another cell (N8) is colored or colored red I want cell I want cell AH8 to return zero. Can I do this with the cell function? If so how?
thank you, Bill
thank you, Bill
Joe Howard
You will have to use VBA.
ASKER
Please help me with that. I have cells N8 and down to N27 and want the results for those cells in AH8 thru AH28
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To do this without using VBA is a two step process.
1. On the Excel Ribbon click "Formulas" and then click on "Name Manager".
2. Select "New" and then enter a name such as "ColoredCells".
3. Jump down to the "Refers to" part and enter the following:
=GET.CELL(63,Sheet1!$N$8)
4. Click OK ot close the Name Manager.
5. In cell AH8 enter the following:
=IF(CellColor=3,"0",IF(Cel
6. If the cell doesn't automatically update you can either hit F9 to force excel to update the calculations. If that doesn't always work you can select cell AH8, put you cursor in the formula bar at the end of the formula and hit Enter (this always works).
Note: In the formula you place in AH8, the number 3 relates to the color Red. Here is a link to all of the colors and their corresponding numbers. If you want to use a different color just change both 3's to the new number.
http://www.smixe.com/excel-color-pallette.html
Also make sure you are selecting the correct color from the color menu. Color 3 (Red) when looking in the color palette is the second one from the left under Standard Colors.
1. On the Excel Ribbon click "Formulas" and then click on "Name Manager".
2. Select "New" and then enter a name such as "ColoredCells".
3. Jump down to the "Refers to" part and enter the following:
=GET.CELL(63,Sheet1!$N$8)
4. Click OK ot close the Name Manager.
5. In cell AH8 enter the following:
=IF(CellColor=3,"0",IF(Cel
6. If the cell doesn't automatically update you can either hit F9 to force excel to update the calculations. If that doesn't always work you can select cell AH8, put you cursor in the formula bar at the end of the formula and hit Enter (this always works).
Note: In the formula you place in AH8, the number 3 relates to the color Red. Here is a link to all of the colors and their corresponding numbers. If you want to use a different color just change both 3's to the new number.
http://www.smixe.com/excel-color-pallette.html
Also make sure you are selecting the correct color from the color menu. Color 3 (Red) when looking in the color palette is the second one from the left under Standard Colors.
ASKER
Works great. Thank you very much, Bill
ASKER
Excero, I am trying to get this to work but having problems. I am using this for cells N8 down to N28 and AH8 to AH28.. I get #NAME? for the answer. =GET.CELL(63,'JOB INFO'!$N$8:$N$28) is what I have entered in name manager under ColoredCells.
This would be great if I could get it to work. Thank you Bill
This would be great if I could get it to work. Thank you Bill
ASKER
Also I do have text in cells N8 thru N28