Would this require a program in EXCEL? multiple conditional statements

[John David]

If there was a N-by-N array of numbers, and within the array, there were
some numbers that fell between one number and another, I want to
highlight that cell. Since there are only 4 conditional statements,
and I want to search for 10 numbers, must I write a program in EXCEL to
do this? So, for instance, I have a 32x32 array, and within the array,
I want to find numbers between 4+/-0.25, 5+/- 0.25, etc. up to 14
+/0.25. I want to color each range a different color.

For those gifted in EXCEL, must I do this with a program in EXCEL or
with conditional statements of some sort?

Thanks for your consideration.

Regards,
John

 

[Ardus Petus]

What if 4.25 < x < 4.75 ?

Cheers

 

[Brian Taylor]

Use this in your conditional formatting:

=OR(MOD(A2,1)<0.25,MOD(A2,1)>0.75)

 

[Brian Taylor]

I didn't take notice that you want each a different color. If you
wanted each a different color you would need to use coding.

 

[John David]

What does the reply by Ardus supposed to mean?

 

[John David]

Where would I start if I needed to code this? I have no prior
experience.

John

 

[Ardus Petus]

Here is some code:
If your values keep changing, you could turn that into an event procedure.

HTH
--
AP

'-------------
Option Explicit

Sub test()
Call SetColors(Range("A1:AF32"))
End Sub


Sub SetColors(rArray As Range)

Const dDelta As Double = 0.25
Dim rCell As Range

For Each rCell In rArray
With rCell
Select Case .Value
Case 4 - dDelta To 4 + dDelta: .Interior.ColorIndex = 33
Case 5 - dDelta To 5 + dDelta: .Interior.ColorIndex = 34
Case 6 - dDelta To 6 + dDelta: .Interior.ColorIndex = 35
Case 7 - dDelta To 7 + dDelta: .Interior.ColorIndex = 36
Case 8 - dDelta To 8 + dDelta: .Interior.ColorIndex = 37
Case 9 - dDelta To 9 + dDelta: .Interior.ColorIndex = 38
Case 10 - dDelta To 10 + dDelta: .Interior.ColorIndex = 39
Case 11 - dDelta To 11 + dDelta: .Interior.ColorIndex = 40
Case 12 - dDelta To 12 + dDelta: .Interior.ColorIndex = 41
Case 13 - dDelta To 13 + dDelta: .Interior.ColorIndex = 42
Case 14 - dDelta To 14 + dDelta: .Interior.ColorIndex = 43
Case Else: .Interior.ColorIndex = xlColorIndexNone
End Select
End With
Next rCell
End Sub
'--------------
"John David" <[email protected]> a
écrit dans le message de (e-mail address removed)...

 

[Ardus Petus]

I wasuzzled: what color should the cell be painted if value is outside
checked range.
I thought it over, and decided it should not be painted.

Cheers,

 

[Brian Taylor]

You would need to cycle through the cells in the range one at a time
and then check to see if you need to apply formatting. Something like:

public sub MyFormatter

dim rng as range
dim arrTemp as variant

'this would be a listing of the color codes you want for each number
arrTemp = array(3,6,43,5,13,16,46,30,20,15,25,55,61,14)

For each rng in range("A14")
if (rng.value mod 1 <.25) or (rng.value mod 1 >.75)
rng.interior.colorindex = arrTemp(round(rng.value,0)-1)
End if
Next rng

End sub

Press alt F11 to go into the VBE. Go Insert Module. Then paste this
code in the white section. The macro should now be available from the
list in Tools -- macros -- play.

 

[John David]

Some people have jobs that they need to complete. Either say somethin
productive or go back to picking your nose in front of the television