Find the median in a color range
I have the following problem to solve in Excel 2010 VBA code
As Range("A:A")
I have a lot of numeric values. Only some of them are highlighted. ( index = 6
[yellow]). I need Excel VBA code to get the median between yellow highlighted values and leave unselected cells outside of calculations.
I tried the code, but it always gives me the first value in the selected range, not the median value of the range.
Dim amarelosMediana As Range
Dim mediana As Double
For Each amarelosMediana In Range([a1], Cells(Rows.count, "A").End(xlUp))
If amarelosMediana.Interior.ColorIndex = 6 Then
mediana = Application.WorksheetFunction.Median(amarelosMediana)
End If
Next amarelosMediana
ActiveSheet.Range("C3") = "Mediana no intervalo de confianca"
ActiveSheet.Range("D3") = mediana
As you can see, I used Application.WorksheetFunction.Median(amarelosMediana)
as mentioned in some tutorials here on stackoverflow to get the Median value, but it doesn't work.
Any ideas
source to share
Edit: Fixed some syntax. You have to pass all selected cells into one master range and then run the function on the main range.
Sub Macro1()
Dim amarelosMediana As Range
Dim tempRng As Range
Dim mediana As Double
Set tempRng = Nothing
For Each amarelosMediana In Range([a1], Cells(Rows.Count, "A").End(xlUp))
If amarelosMediana.Interior.ColorIndex = 6 Then
If tempRng Is Nothing Then
Set tempRng = amarelosMediana
Else
Set tempRng = Union(tempRng, amarelosMediana)
End If
End If
Next amarelosMediana
mediana = Application.WorksheetFunction.Median(tempRng)
ActiveSheet.Range("C3") = "Mediana no intervalo de confianca"
ActiveSheet.Range("D3") = mediana
End Sub
source to share