conditional format

[Mon Wimp]

Hi,
Can a cell change color if multiples of 7 are entered in a cell
Thanks Allan

 

[VoG]

Try a CF formula like

=AND(A1<>"",MOD(A1,7)=0)

 

[Weaver]

You would need to use 'formula is' from the first drop down list and set the formula to:

=MOD(A1,7)=0

Then design your conditional format as normal.

Note, A1 should refer to the top left cell in the block you're adding the conditional format to, and you should select the block beginning with this cell, otherwise you may have problems with referencing.

If it's only one cell, then of course the address should be the cell's address.

You could use this form:

=MOD(INDIRECT(CELL("address")),7)=0

but that seems a bit long winded.

edit: VoG's solution is better if you need to ignore blanks, but in terms of cell referencing, mine's worth a look too. You could probably adapt them to get the best of both worlds.

 

[Jude24]

Hello,

Your formulars are great but just to add to what Vog has;

If you are running a 2007 version perform the following;

0: Highlight the colunm you want to perform the conditional format on. Say the data is contained from colunm A1:A500
1: Select "Home" ribbon, and click on the drop-down of Conditional Formatting from the "Styles" tab.
2: Select "New Rules" from the list
3: Click on use a formular to determine which cell to format
4: Copy Vog's formular =AND(A1<>"",MOD(A1,7)=0) into the "Format values where this formula is true:"
5: Change the preview style by clicking on format .... Change the color and the font style
6: click on ok and ok.

Hope this helps...

Regards

 

[Mon Wimp]

thanks for the responses, I have not been able to get it to work. Maybe because i am using 2010. Oooops did not mention it sorry. In conditional formatting box, there is no ? this true choice.
Allan

 

[VoG]

Click New Rule, Click Use a formula...

 

[Mon Wimp]

i have got it, sorry it was down bad keystroking. many thanks