Write array of colours to excel?

[osx99]

I have a range in excel which gets loaded into an array, I then perform calculations on the array and write it back to the same range.

I would like to be able to colour the cells say blue if the new cell value is less than previous and red if greater than previous value.

The only way I can think of currently is to compare every value between the initial array and newly calculated array and change the corresponding cell formatting individually. Is there a quicker more efficient method than this? Eg is there a method to write a range of colours/formats back to excel in 1 hit to maintain speed within the code?

Thanks,
Os

 

[Gruuuu]

Can you show us your existing code? You say you load a range of cells into an array; what you mean by that is a little fuzzy. Do you create an array of Range objects, or is it an array of strings that contain the cell reference?

 

[osx99]

This is a snippet

Set rng = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("BH5:BM55")
        strArray = rng
        BA_Changes = Target
        
      

        For i = 5 To UBound(BA_Changes)
                


                strArray(i - 4, 6) = BA_Changes(i, 8) - strArray(i - 4, 5)
                strArray(i - 4, 5) = BA_Changes(i, 8)
                
                Select Case strArray(i - 4, 4)
                Case "", Is > BA_Changes(i, 8)
                If BA_Changes(i, 8) <> 0 Then
                strArray(i - 4, 4) = BA_Changes(i, 8)
                Else
                strArray(i - 4, 4) = 1001
                End If

                End Select
                
                
  

                strArray(i - 4, 3) = BA_Changes(i, 6) - strArray(i - 4, 2)
                strArray(i - 4, 2) = BA_Changes(i, 6)
                
                Select Case strArray(i - 4, 1)
                Case "", Is > BA_Changes(i, 6)
                If BA_Changes(i, 6) <> 0 Then
                strArray(i - 4, 1) = BA_Changes(i, 6)
                Else
                strArray(i - 4, 1) = 1001
                End If


                End Select
                
        Next i

    Else

        For i = 1 To UBound(strArray)

                strArray(i, 1) = 1001
                strArray(i, 2) = ""
                strArray(i, 3) = ""
                strArray(i, 4) = 1001
                strArray(i, 5) = ""
                strArray(i, 6) = ""
                
        Next i
        
        
    End If
    ThisWorkbook.Sheets(Target.Worksheet.Name).Range("bh5:bm55").Value = strArray

 

[SkipVought]

Hi,

I'd copy the original range to another location before you do your analysis and writeback.

Then do a simple Conditional Formatting to compare to new value to the old.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}