Forum Discussion
Change the cell's font color in a cell based on other cell's values not using conditional formatting
Hello. Can somebody help me code in each cell how to change the font color based upon another cell's value? I know how to do it in excel VBA using vbBlack and vbWhite or RGB(0,0,0) and RGB(255,255,255) but would like to do it within the cell's code itself. I don't know the syntax within a cell . A sample is within cell A5:
IF(AND(A10 <> "", A10>=0, A10, IF(A20 > 0, A20, 0)), IF(OR(AND(A10 <> "", A10>=0), A20 > 0), Cell.Font.Color =vbBlack, Cell.Font.Color =vbWhite)
I don't know the syntax within a cell. Another way within cell A5:
IF(AND(A10 <> "", A10>=0, A10, IF(A20 > 0, A20, 0)), IF(OR(AND(A10 <> "", A10>=0), A20 > 0), Cell.Font.Color =RGB(0,0,0), Cell.Font.Color =RGB(255,255,255))
I've used conditional formatting but can't show the 0 because I've changed the cell to white based upon it being 0. I don't want to use conditional formatting if I can help it. I'd like to change cell A5 based upon the values of cell A10 or A20 as described above. Thank you for all those willing to help. Tommy 🙂
There are three ways you can make the font color of a cell change automatically:
- For numbers only, you can specify up to two different colors for ranges of values of the cell itself. For example, the custom number format [Green][<1000]0;[Red][>5000]0;0 will display numbers <1000 in green, numbers >5000 in red, and numbers in between in the standard color. A number format cannot be based on the value of another cell.
- Using conditional formatting.
- Using a VBA event procedure in the worksheet module (or in the workbook module).
A formula in a cell cannot directly change the cell's font color based on the value of another cell. (If somebody can prove me wrong, I'd be delighted...)
- I created a VBA macro just in case another solution isn't available.
- hey , could you share the vba macro?
HansVogelaar Thank you for your advice.
