OpenOffice feature query

[andygates]

One of the features I'm utterly besotted with in Excel 07 is the new colour-range conditional formatting.  It means you can take a table of numbers and overlay a range of colours like a "heat map" - great for quickly spotting trends and outliers.

Does that exist in OO's Calc?

[Pingu]

Yes

[andygates]

Yay, unleash the Portable App!

[andygates]

Pingu, you lie!

I have formatting, but no value gradients.  v3.1.  Either I'm being thick, or your advice is like unto an MP's expenses claim! 

[Pingu]

Didn't see your 'colour-range' stipulation - ruddy skim reading  Sorry, I'll pay back the difference 

[inc]

You could set three ranges for your values  OpenOffice Calc Tips : Conditional Formatting II , but I don't think this is exactly what you want.

[Lucky]

Andy, the following OpenOffice macro should do roughly what you want. It shades the lowest value white, the highest value red, and values in between various shades of pink.

Slight limitation: it only works on rectangular selections. Making it work on multiple ranges is rather more complicated.

Feel free to use as you wish.

Code: [Select]

Sub ColourSelectedCells
  oSelect=ThisComponent.CurrentSelection
  oColumn=oselect.Columns
  oRow=oSelect.Rows
  Dim valMax
  Dim valMin
  Dim valTmp
 
  valMax = oselect.getCellByPosition(0,0).Value
  valMin = oselect.getCellByPosition(0,0).Value
 
  For nc = 0 To oColumn.getCount-1
    For nr = 0 To oRow.getCount-1
      oCell = oselect.getCellByPosition(nc,nr)
      If oCell.Value > valMax Then
      valMax = oCell.Value
      End If
      If oCell.Value < valMin Then
      valMin = oCell.Value
      End If  
    Next nr
  Next nc
 
  If (valMax-valMin) <> 0 Then
    For nc = 0 To oColumn.getCount-1
      For nr = 0 To oRow.getCount-1
        oCell = oselect.getCellByPosition(nc,nr)
        valTmp = 255*(valMax-oCell.Value)/(valMax-valMin)
        oCell.CellBackColor = RGB (255, valTmp, valTmp)
      Next nr
    Next nc
  End If
 
End Sub


[andygates]

Thanks, but "macro" versus "just click it" means Excel '07 wins the Kewpie doll this time around. 

(the heat-map thing is so awesome that it's a killer app all on its own.  It's as sexy as charts were back in the day)

[David Martin]

Thanks, but "macro" versus "just click it" means Excel '07 wins the Kewpie doll this time around. 

(the heat-map thing is so awesome that it's a killer app all on its own.  It's as sexy as charts were back in the day)

That does sound interesting.  I have excel 2003. is it in that?

The OO macro sounds good. I will have to investigate.

..d

[David Martin]

Thanks, but "macro" versus "just click it" means Excel '07 wins the Kewpie doll this time around. 

Macro= write it in once. Then just click it.

Decent colour selection would be nice. (red/green and blue/white/yellow are 'standards' for this in the field) Might look into that for generating pretty output.

..d