Make all number values BOLD that meet criteria ?

B

brazen234

New to the board !
I'm sure my question is easy for all you gurus !
I've read around and it looks like I will need to use VBA to do what
want but I want to make sure.

I am working with lotto numbers so you have five columns x about 150
rows , each cell with one number value .
I would like to make BOLD only the prime numbers.

THEN...I want to have excel count how many prime numbers in each row.
suppose you could just have excel count the number of bold values afte
you have excel turn all prime bold.

Anyway, conditional formating gives me only 3 filters. There are 1
prime numbers I'm working with.

Thanks
 
H

hcj

you need only set one condition - try this:

data in A1:En

create a vertical lookup table containing all the numbers
and a second column containing "N" or "P", "P" marking the
primes. Select your data area. In Conditional
formatting, select Formula Is, then =if(vlookup(a1,lookup
array in absolute reference terms,2)="P","TRUE","FALSE").
This returns a true condition for primes, which then
applies your selected format.
HTH
 
F

Frank Kabel

Hi
for formating prime numbers try the following:
1. Insert the following user defined function in one of your modules
(function posted by Vasant Nanavati):
Function IsPrime(Num As Long) As Boolean
Dim i As Long
If Num < 2 Or (Num <> 2 And Num Mod 2 = 0) Then Exit Function
For i = 3 To Sqr(Num) Step 2
If Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function

2. Now select your range of numbers (lets assume the range A1:A100).
Goto 'Format - Conditional Format' and enter the following formula:
=ISPRIME(A1)
and choose a format (bold)
 
B

Bob Phillips

You could use CF.

Add thes UDFs

'-----------------------------------------------------------------
Function IsRangePrime(rng As Range) As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim j As Long
Dim row As Range
Dim cell As Range
Dim aryPrime As Variant
If rng.Cells.Count = 1 Then
aryPrime = IsPrime(rng.Value)
Else
aryPrime = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
aryPrime(i, j) = IsPrime(cell.Value)
Next cell
Next row
End If
IsRangePrime = aryPrime
End Function

'-----------------------------------------------------------------
Function IsPrime(num As Long) As Boolean
'-----------------------------------------------------------------
Dim i As Long
IsPrime = True

If num Mod 2 = 0 Then
IsPrime = False
Else
For i = 3 To num ^ 0.5 Step 2
If num Mod i = 0 Then
IsPrime = False
End If
Next i
End If

End Function


select all the cells, goto CF, add a formula of =IsRangePrime(A1), and
format as bold.

To count them, use something like

=SUMPRODUCT(--(IsPrime(A1:H500)))



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Sorry, the count should be

=SUMPRODUCT(--(IsRangePrime(A1:H500)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
You could use CF.

Add thes UDFs

'-----------------------------------------------------------------
Function IsRangePrime(rng As Range) As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim j As Long
Dim row As Range
Dim cell As Range
Dim aryPrime As Variant
If rng.Cells.Count = 1 Then
aryPrime = IsPrime(rng.Value)
Else
aryPrime = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
aryPrime(i, j) = IsPrime(cell.Value)
Next cell
Next row
End If
IsRangePrime = aryPrime
End Function

'-----------------------------------------------------------------
Function IsPrime(num As Long) As Boolean
'-----------------------------------------------------------------
Dim i As Long
IsPrime = True

If num Mod 2 = 0 Then
IsPrime = False
Else
For i = 3 To num ^ 0.5 Step 2
If num Mod i = 0 Then
IsPrime = False
End If
Next i
End If

End Function


select all the cells, goto CF, add a formula of =IsRangePrime(A1), and
format as bold.

To count them, use something like

=SUMPRODUCT(--(IsPrime(A1:H500)))



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
F

Frank Kabel

Hi
forgot yur last question. Add this additional UDF:

Function IsPrime_Array(rng As Range) As Variant
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryprimes As Variant

If rng.Cells.Count = 1 Then
aryprimes = IsPrime(rng.Value)
Else
aryprimes = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
aryprimes(i, j) = IsPrime(cell.Value)
Next cell
Next row
End If
IsPrime_Array = aryprimes
End Function


Now use the following formula
=SUMPRODUCT(--(ISPRIME_ARRAY(A1:A20))

--
Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel said:
Hi
for formating prime numbers try the following:
1. Insert the following user defined function in one of your modules
(function posted by Vasant Nanavati):
Function IsPrime(Num As Long) As Boolean
Dim i As Long
If Num < 2 Or (Num <> 2 And Num Mod 2 = 0) Then Exit Function
For i = 3 To Sqr(Num) Step 2
If Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function

2. Now select your range of numbers (lets assume the range A1:A100).
Goto 'Format - Conditional Format' and enter the following formula:
=ISPRIME(A1)
and choose a format (bold)




--
Regards
Frank Kabel
Frankfurt, Germany

brazen234 > said:
New to the board !
I'm sure my question is easy for all you gurus !
I've read around and it looks like I will need to use VBA to do
Click to expand...
what
I
want but I want to make sure.

I am working with lotto numbers so you have five columns x about 1500
rows , each cell with one number value .
I would like to make BOLD only the prime numbers.

THEN...I want to have excel count how many prime numbers in each
Click to expand...
row.
Click to expand...
 
H

hcj

sorry - missed a point - you want to count the number of
primes as well as mark them:
Instead of "P" and "N", use 1 and 0, 1 marking primes, and
revise the test from ="P" to =1
In a column next to your data use =vlookup(a1,the lookup
array in absolute reference terms,2)+vlookup(b1,array,2)
+...+vlookup(e1,array,2). This will give you the number
of primes in the row.

Sorry for the hiccup
HTH
 
B

brazen234

Thanks all..I like the quick replies. I have alot of studing to do abou
excel to do even what you ask for I don't even know what UDF stands fo
or what modules are about.

Thanks again
 
B

Bob Phillips

Yeah, I noticed that you plagiarist<vbg>.

I used the same technique for this, but I implemented it as separate
functions. Life's rich tapestry!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
F

Frank Kabel

Hi Bob
you know as a consulatnt I'm trained to 'steal' other's ideas and to
market them as brand new :)
And in addition I also used two functions (I just pasted the second one
separately...)
 
B

Bob Phillips

You do know it's licensed?

Do you know, I missed that your second function wasn't an all-embracing. I
saw that you had implemented the array technique, and I guess I just saw
what I expected thereafter.

Bob
 
F

Frank Kabel

Hi Bob
license fee?
A pint of some English beer or better an Australian Shiraz <vbg>
 
B

brazen234

Frank said:
Hi
for formating prime numbers try the following:
1. Insert the following user defined function in one of your modules
(function posted by Vasant Nanavati):
Function IsPrime(Num As Long) As Boolean
Dim i As Long
If Num < 2 Or (Num <> 2 And Num Mod 2 = 0) Then Exit Function
For i = 3 To Sqr(Num) Step 2
If Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function

2. Now select your range of numbers (lets assume the range A1:A100).
Goto 'Format - Conditional Format' and enter the following formula:
=ISPRIME(A1)
and choose a format (bold)
Click to expand...


I have taken my time and I learned some more on UDF's but I cannot mak
things work to make bold all prime numbers with the way you and Bo
Phillips describe.

I have your UDFs and Bob's in the personal.xls folder and I have als
tried it by putting them directly in the worksheet I'm working in.
I select the range of cells I want checked for primes and I go to C
and do "Formula Is" =IsPrime(A1). I have also done =IsRangePrime(A1)
Then I format bold but to no avail.
No changes.

Any ideas ?

Thanks
 
F

Frank Kabel

Hi
try to put this macro in your workbook not in your personal.xls. Does
it work this way?

--
Regards
Frank Kabel
Frankfurt, Germany

brazen234 > said:
Frank said:
Hi
for formating prime numbers try the following:
1. Insert the following user defined function in one of your modules
(function posted by Vasant Nanavati):
Function IsPrime(Num As Long) As Boolean
Dim i As Long
If Num < 2 Or (Num <> 2 And Num Mod 2 = 0) Then Exit Function
For i = 3 To Sqr(Num) Step 2
If Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function

2. Now select your range of numbers (lets assume the range A1:A100).
Goto 'Format - Conditional Format' and enter the following formula:
=ISPRIME(A1)
and choose a format (bold)
Click to expand...


I have taken my time and I learned some more on UDF's but I cannot make
things work to make bold all prime numbers with the way you and Bob
Phillips describe.

I have your UDFs and Bob's in the personal.xls folder and I have also
tried it by putting them directly in the worksheet I'm working in.
I select the range of cells I want checked for primes and I go to CF
and do "Formula Is" =IsPrime(A1). I have also done =IsRangePrime(A1).
Then I format bold but to no avail.
No changes.

Any ideas ?

Thanks!
Click to expand...
 
B

brazen234

Frank said:
Hi
try to put this macro in your workbook not in your personal.xls
Does
it work this way?
Click to expand...

I have tried that. I have put the functions in the workbook and eve
the indivdual worksheet.
The functions show up just fine when I go to "paste function > use
defined" so excel sees them.

I select the range of cells go to CF, select "Formula Is" and hav
tried both "=IsPrime(A1)" and "=IsRangePrime(A1)". I have bot
functions in the workbook, but I still get no results. I have eve
tried different fomats, like making the text a different color, but
get nothing.

By the way , what does the "(A1)" do
 
D

Dave Peterson

Conditional formatting won't let you use another workbook--even if it's the UDF.

You could use a helper cell and put the reference to the UDF in that cell, then
use that cell as the reference in your conditional formatting formula.

But I think the answer may be even simpler.

Keep the UDF in the same workbook.

=isprime(a1)

will format the cell if A1 is prime. I'm betting you want to just change the
formula to point at the cell you need.

If you're applying CF to E92, then
=isprime(e92)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula listing words that meet criteria 5
Counting rows, then counting values. 7
Counting Consecutive Cells that meet Criteria 2
Range, select, and printing arrays 5
Ranking Values that Meet a Certain Criteria 3
Apply cell formatting using VBA 1
Excel Opening Up Different On Different Computers..... 1
Count unique records that also meet criteria 3

Top