Prev: Look for Oldest date among different dates
Next: Excel Macro
From: Live Long & Prosper on 16 May 2010 18:09
Playing with Excel I'm trying to conditionally format cells that divided
evenly in a range, heres a simple example :)

In cell A1 I put '=50000000/row()' I then extend (drag) that for 100 rows.
What I'd like to do is then select A1:A100 and conditionally format so the
ones that divded with no remainder are highlighted.

I can't figure out how to format a whole range with a formula.
From: Per Jessen on 16 May 2010 18:59
You do not need your formula in A1 and down.

Just select A1:A100, goto Format > Conditional Formatting > Formula
is: '=Mod(Row(),2)=0'

Select formatting as desired.

Regards,
Per

On 17 Maj, 00:09, "Live Long & Prosper" <sp...(a)the.helm> wrote:
> Playing with Excel I'm trying to conditionally format cells that divided
> evenly in a range, heres a simple example :)
>
> In cell A1 I put '=50000000/row()' I then extend (drag) that for 100 rows.
> What I'd like to do is then select A1:A100 and conditionally format so the
> ones that divded with no remainder are highlighted.
>
> I can't figure out how to format a whole range with a formula.

From: Live Long & Prosper on 16 May 2010 20:58
Per Jessen <perjessen69(a)hotmail.com> wrote in
news:f793dac6-b8fd-4a8e-becc-042d6e274aa0(a)u7g2000vbq.googlegroups.com:

> You do not need your formula in A1 and down.
>
> Just select A1:A100, goto Format > Conditional Formatting > Formula
> is: '=Mod(Row(),2)=0'
>
> Select formatting as desired.
>
> Regards,
> Per
>
> On 17 Maj, 00:09, "Live Long & Prosper" <sp...(a)the.helm> wrote:
>> Playing with Excel I'm trying to conditionally format cells that
>> divided evenly in a range, heres a simple example :)
>>
>> In cell A1 I put '=50000000/row()' I then extend (drag) that for 100
>> rows. What I'd like to do is then select A1:A100 and conditionally
>> format so the ones that divded with no remainder are highlighted.
>>
>> I can't figure out how to format a whole range with a formula.
>
>

I'm not looking for the values that are even (divided by 2), I'm looking
for vlaues that have 0 as a remainder. In the above example here are the
top 10 results {a1 thru a10 has 50,000,000/row()}:

50,000,000.00
25,000,000.00
16,666,666.67
12,500,000.00
10,000,000.00
8,333,333.33
7,142,857.14
6,250,000.00
5,555,555.56
5,000,000.00


I want the ones that end in zero to be highlighted :)
From: Chip Pearson on 17 May 2010 08:23
Select A1:A100 and then open the Conditional Formatting dialog. Choose
"Use a formula" and enter the following formula:

=INT(A1)=A1

and then choose your formatting. If your range does not start in A1,
change both instances of A1 in the formula to the first cell in the
list of cells. Excel will automatically change the A1 references to
A2, A3, etc for the cells in the selected range.

This will format those cells in A1:A100 that are integers.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On 16 May 2010 22:09:34 GMT, "Live Long & Prosper" <spock(a)the.helm>
wrote:

>Playing with Excel I'm trying to conditionally format cells that divided
>evenly in a range, heres a simple example :)
>
>In cell A1 I put '=50000000/row()' I then extend (drag) that for 100 rows.
>What I'd like to do is then select A1:A100 and conditionally format so the
>ones that divded with no remainder are highlighted.
>
>I can't figure out how to format a whole range with a formula.
From: Live Long & Prosper on 17 May 2010 09:46
Chip Pearson <chip(a)cpearson.com> wrote in
news:02d2v5d00pa3440qp8os5fadr0lirrurs3(a)4ax.com:

> Select A1:A100 and then open the Conditional Formatting dialog. Choose
> "Use a formula" and enter the following formula:
>
> =INT(A1)=A1
>
> and then choose your formatting. If your range does not start in A1,
> change both instances of A1 in the formula to the first cell in the
> list of cells. Excel will automatically change the A1 references to
> A2, A3, etc for the cells in the selected range.
>
> This will format those cells in A1:A100 that are integers.
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> On 16 May 2010 22:09:34 GMT, "Live Long & Prosper" <spock(a)the.helm>
> wrote:
>
>>Playing with Excel I'm trying to conditionally format cells that
>>divided evenly in a range, heres a simple example :)
>>
>>In cell A1 I put '=50000000/row()' I then extend (drag) that for 100
>>rows. What I'd like to do is then select A1:A100 and conditionally
>>format so the ones that divded with no remainder are highlighted.
>>
>>I can't figure out how to format a whole range with a formula.
>

Simple once ya know how. In the past (finger poking problems no doubt) I
couldn't get that to work. It would ALWAYS put $A$1 in all 100 boxes,
now it does not. Any hoo, THANKS!
 | 
Pages: 1
Prev: Look for Oldest date among different dates
Next: Excel Macro