Can you color cells with Array Formula?

D

davidm

If I have an assortment of odd and even values in range a1:j100, usin
the Array formula* {=If(Mod(a1:j100,2),"",a1:j100)*} will clear out al
the odds, leaving the evens.

Is there a way I can add to this formula to color the qualifying cell
(odds in this instance)? I know Conditional formatting is perfectl
suited for the job but I am looking for a departure from this.

Thanks.

davi
 
M

Myles

Biff said:
why?
Click to expand...

The greatest virtue in using any software, no less Excel and other
spreadsheets, is the flexibility of turning your hand at various
alternative solutions. You do want to be conservatively booged down to
routines, do you?
 
B

Biff

The greatest virtue in using any software, no less Excel and other
spreadsheets, is the flexibility of turning your hand at various
alternative solutions.
Click to expand...

A far higher virtue is knowing that the solution at hand is the most
effiicient use of the software and system resources!

I know that =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 certainly does work, but 100% of
the time, I'll use =SUM(A1:A10). Booged down to routines has benefits!

Biff
 
M

Myles

If finding alternative ways around a problem were not important, the
great minds behind EXCEL wouldn't have, for example, bothered to spoil
us with SEARCH versus FIND; REPLACE versus SUBSTITUTE worksheet
functions-to cite but two examples. Next time, it will be some computer
boffin pontificating that SEARCH is slower than FIND and that REPLACE is
more resource-friendly than SUBSTITUTE.
 
D

davidm

If I may append to my original post, should it be possible to color with
an array formula in an instance like I demonstrated, we could then
circumvent the 3-condition limitation in Conditional Formatting by
using (array) formula to evaluate up to at least 7 conditions (or as
many IF evaluations as possible). It is not just mere fancy.

David
 
B

Bob Phillips

The answer is no, as far as Excel is concerned, but you can use event code
to simulate it, such as


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

I think you will find that FIND/SEARCH and REPLACE/SUBSTITUTE work slightly
differently, it was not a case of providing two ways to do the same thing,
but more likely responding to functions in other products, e.g. Lotus 123.
Why they didn't provide one function in each case with variations is beyond
me, I can never remember whether it is FIND or SEARCH that is
case-sensitive.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

davidm

Thanks Bob for the insight. I had always imagined that it would b
immensely helpful to endue worksheet (array) functions with additiona
functionality to do a few more things we normally leave to vba and fo
that matter UDF. Reason? Much as I love codes, Formulas are in the mai
faster to construct and friendlier to pass on to others in a workgrou
setting.


Davi
 
Top