Simplifying a Conditional Formatting Statement

**ChemistB** · 04-02-2007, 01:49 PM

I am looking to conditionally format cells to look for trends in data.

The first trend would be to conditionally format if there are 9 cells in a row either greater than or less than the mean (in D4) (data is arraigned in column F)

This formula would be
=AND(ISNUMBER(F19),OR(AND(F19<$D$4,F18<$D$4,F17<$D$4,F16<$D$4, F15<$D$4,F14<$D$4, F13<$D$4,F12<$D$4,F11<$D$4), AND(F19>$D$4, F18>$D$4,F17>$D$4,F16>$D$4, F15>$D$4,F14>$D$4,F13>$D$4, F12>$D$4, F11>$D$4)))

The second trend would be if 14 cells in a row are seesawing up and down.
This formula would be
=AND(ISNUMBER(F19),AND(F19>F18,F18<F17,F17>F16, F16<F15, F15>F14, F14<F13, F13>F12, F12<F11, F11>F10, F10<F9, F9>F8, F8<F7, F7>F6))

Any way to simplify these formulas within the conditional format? What I really want to do is combine the two into one format (=OR(A,B) but it's too long. I have other conditional formatting to do. Any thoughts?

ChemistB

**duane** · 04-02-2007, 02:19 PM

would you consider a macro that ran on anychanges to the workbook?

**ChemistB** · 04-02-2007, 02:57 PM

Originally Posted by duane

would you consider a macro that ran on anychanges to the workbook?

I might.

chemistb

**Dav** · 04-02-2007, 03:41 PM

the first condition is

and(isnumber(f19),or(countif(f19:f11,"<"&$d$4)=9,(countif(f19:f11,">"&$d$4)=9))

Thinking about the second condition!

regards

Dav

**duane** · 04-02-2007, 07:25 PM

' open VBE (alt F11) and plunk this in module for the sheet you are using
'

Private Sub worksheet_change(ByVal mydata As Range)
' 
' colorindex 3 is red, 6 is yellow
' I changed the color of cell a16
'
mytest = Range("d4").Value
Sheets("sheet1").Activate
If Range("f19").Value > mytest And Range("f18").Value > mytest _
   And Range("f17").Value > mytest And Range("f16").Value > mytest _
   And Range("f15").Value > mytest And Range("f14").Value > mytest _
   And Range("f13").Value > mytest And Range("f12").Value > mytest _
   And Range("f13").Value > mytest Then Range("a16").Interior.ColorIndex = 3 _
Else If Range("f19").Value < mytest And Range("f18").Value < _
   mytest And Range("f17").Value < mytest And Range("f16").Value < _
   mytest And Range("f15").Value < mytest And Range("f14").Value < _
   mytest And Range("f13").Value < mytest And Range("f12").Value < _
   mytest And Range("f13").Value < mytest Then Range("a16").Interior.ColorIndex = 6

You could then code your second copndition in a similar fashion

**rylo** · 04-03-2007, 01:02 AM

HI

In a general module enter the following functions

Function inarow(rng As Range, aa) as boolean
  inarow = False
  Set wrkrng = rng.Resize(rng.Rows.Count - 9, 1)
  For Each ce In wrkrng
    If WorksheetFunction.CountIf(ce.Resize(9, 1), "<" & aa) = 9 Then inarow = True
    If WorksheetFunction.CountIf(ce.Resize(9, 1), ">" & aa) = 9 Then inarow = True
  Next ce
End Function

Function seesaw(rng As Range) As Boolean
  seesaw = True
  Set wrkrng = rng.Offset(1, 0).Resize(rng.Rows.Count - 2, 1)
  For Each ce In wrkrng
    If ce.Offset(-1, 0) > ce And ce.Offset(1, 0) < ce Then seesaw = False
    If ce.Offset(-1, 0) < ce And ce.Offset(1, 0) > ce Then seesaw = False
  Next ce
End Function

If your data is in the range F1:F200, and the mean is in D4 then your conditional formatting formula is:

=OR(inarow(F1:F200,D4),seesaw(F1:F200))

HTH

rylo

Simplifying a Conditional Formatting Statement

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simplifying a Conditional Formatting Statement

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions