Excel database query

  • Thread starter Thread starter dancecommander81
  • Start date Start date
D

dancecommander81

Hello,

I have created a spreadsheet that exports data from Navision. Just a
simple query, salesperson code and sales, for a specific list of
salespeople for a specified date range. I have created a graph from the
data. The graph is displayed to the sales team on an screen in the
sales room. I was wondering if it is possible to do something (I have
no idea what!?) that would colour the greatest value differently to the
others in the graph. Basically 'if the value of sales in this cell are
greater than all other salespeoples sales then colour datapoint
yellow'. I know it's a long shot, but can anyone help?

Cheers,

Richard Thorneycroft
 
Ed,

Thanks for the help, that is very impressive, but how do I implement
that within my existing spreadsheet?

Thanks,

Rich
 
Basically I want to say IF CELL ?? IS = TO MAX OF ??:?? THEN COLOUR THE
DATA POINT YELLOW

I can do this using conditional formatting on the worksheet but not in
the chart (IF Cell value is equal to =MAX($B$5:$B$14) fill cell yellow).
 
Hi Rich,

1) copy this code into a module on your workbook.
(press {Alt}-{F11} then insert new module, then copy the code)


' start copy here
' =========================================================
Sub FormatChart()
Dim oCht As ChartObject
Dim ser As Series
Dim i As Integer
Dim strValues As String
Dim ind As Integer

For Each oCht In ActiveSheet.ChartObjects
For Each ser In oCht.Chart.SeriesCollection

i = FindStringLast(ser.FormulaLocal, ",")
strValues = Left(ser.FormulaLocal, i)
i = FindStringLast(strValues, ",")
strValues = Right(strValues, Len(strValues) - i - 1)

For i = 1 To ActiveSheet.Range(strValues).Cells.Count
With ActiveSheet.Range(strValues)
ind = FormatConditionIndex(.Cells(i))
If ind = 0 Then
ser.Points(i).Interior.ColorIndex = .Cells(i).Interior.ColorIndex
Else
ser.Points(i).Interior.ColorIndex =
..FormatConditions(ind).Interior.ColorIndex
End If
End With
Next i
Next
Next
End Sub

Function FindStringLast(ByVal strOrig As String, ByVal strToFind) As Integer
Dim i As Integer
Dim origLen As String
Dim stLen As Integer
origLen = Len(strOrig)
For i = 1 To origLen
stLen = Len(strOrig)
If Mid(strOrig, stLen, 1) = strToFind Then
FindStringLast = origLen - i
Exit Function
Else
strOrig = Left(strOrig, stLen - 1)
End If
Next
End Function

Function FormatConditionIndex(rng As Range) As Integer
Dim j As Integer
Dim valCond1 As Double
Dim valCurr As Double
' returns the index of the current format condition
' that applies to the range rng
' Note: only works if the condition is a number
' NOT a formula
' only tests for > and <

' test that rng is single cell
If rng.Cells.Count <> 1 Then
FormatConditionIndex = 0
Exit Function
End If

' loop through format conditions
With rng
valCurr = .Value
For j = 1 To .FormatConditions.Count
With .FormatConditions(j)
valCond1 = Evaluate(.Formula1)

Select Case .Operator
Case xlEqual
If valCurr = valCond1 Then FormatConditionIndex = j

Case xlGreater
If valCurr > valCond1 Then FormatConditionIndex = j
Case xlLess
If valCurr < valCond1 Then FormatConditionIndex = j
Case Else
FormatConditionIndex = 0
End Select
End With
Next j
End With

End Function

' =========================================================
' end copy here

2) Then select the data series for your chart and use conditional
formatting as you describe below.

3) Then run the macro FormatChart

Ed Ferrero
http://edferrero.m6.net
 
Hi Ed,

It doesn't work :(

But I'm sure it's me and not your code.

I copied it and it runs but doesn't seem to do anything. My conditional
formatting says if cell value is equal to Max($B$5:$B$14) then fill
pattern Yellow. That works fine on the sheet. I changed the condition
to something simpler, like if greater than 10000 fill yellow. But on
running the Macro there were still no changes.

Let me know what you need to know.

Thanks so much for your help!

Cheers,

Rich
 
Hi Rich,

I have been travelling last week so did not get a chance to look at the
newsgroups.

Where is your chart? If it is an embedded chart on the same sheet as the
data, then the macro should work.

Otherwise, send me (not the newsgroup) a copy of the worksheet and I will
see what is wrong.

Ed Ferrero
http://edferrero.m6.net
 
Back
Top