Sleeper: Highlighting Cell in Variant

[karrotman]

Hi guys,

So I've been working on a simple linear interpolation code. Basically it takes a matrix of timedata and interpolates from the lowest time to the highest. Everything runs smoothly. What I would like is for my VBA code to spit out a matrix with the original values HIGHLIGHTED.

Keep in mind what my code does:

1. It accepts the input data
2. It creates a variant called MAT and fills it with dates and Xs for data
3. It interpolates and changes the Xs for the interpolated values
4. It sets the function (LinearInterpolation() ) to MAT

Now I would just use:CellName.Interior.ColorIndex = 4, but I know that will not work because at that point I am working on the virtual matrix MAT rather then the actual workbook. Does anybody have a solution for this? I know I can scan through the cells, but I want to keep this as dynamic as possible. In other words I want the function to do this without knowing where the interpolation is taking place.


This is what I have:
!


...and this is what I want:


Thanks!

[CatDaddy]

post the rest of your code

[karrotman]

Ok, you asked for it...

Function LinearInterpolation(TimeData As Range, m As Integer, T As Integer) As Variant
'PREPARING VARIABLES
Dim TIME1, TIME2, DATA1, DATA2, TIMEX, NewValue As Double
TIME1 = 0
TIME2 = 0
DATA1 = 0
DATA2 = 0
NewValue = 0
Dim NODES As Integer
NODES = T * m + 1
Dim i, j, z As Integer
'THE RESULTING MATRIX
Dim Mat As Variant
ReDim Mat(1 To NODES, 1 To 2)
'FINDS THE LOWEST STARTING TIME
z = TimeData(1, 1)
For i = 1 To Application.WorksheetFunction.Count(TimeData) / 2 'THIS RETURNS HOW MANY TIME VALUES WERE ORIGINALLY GIVEN
    If TimeData(i, 1) < z Then z = TimeData(i, 1)
Next i
Mat(1, 1) = z
'FILLS IN THE FIRST DATA VALUE
For i = 1 To NODES
    If Mat(1, 1) = TimeData(i, 1) Then Mat(1, 2) = TimeData(i, 2)
Next i
'NOW THE ACTUAL PROGRAM BEGINS
'FILLS ALL OF THE DATA IN MAT WITH XS FOR LATER USE
For j = 2 To NODES - 1
    Mat(j, 2) = "X"
Next j
'POPULATES THE DATES SECTION OF MAT BY INCREASING EACH ROW BY 1/M
For i = 2 To NODES
    Mat(i, 1) = Mat(i - 1, 1) + (1 / m)
Next i
'CHECKS FOR SIMILARITIES IN DATES AND FILLS IN DATA USING THE GIVEN VALUES
For j = 2 To NODES
    For i = 1 To NODES - 1
        If TimeData(i, 1) = Mat(j, 1) Then Mat(j, 2) = TimeData(i, 2)
    Next i
Next j
Dim CHECK, ABORT As Integer
CHECK = 0
j = 1
'DONE PREPARATION
'INITIATION OF FILLING A SINGLE X CELL WITH INTERPOLATED VALUE
Do While CHECK = 0
ABORT = 0
j = 1
i = j + 1
Do While ABORT <> 1
    Do While i < NODES + 1
If Mat(j, 2) = "X" Then
            ABORT = 1
            Mat(j, 2) = Mat(i, 2)
            TIME2 = Mat(i, 1)  'THE TIME IN THE FUTURE (ti+1) FOR INTERPOLATION
        End If
i = i + 1
    Loop
        j = j + 1
        i = j + 1
Loop
TIME1 = Mat(j - 2, 1) 'THE TIME IN THE PAST (ti-1) FOR INTERPOLATION
TIMEX = Mat(j - 1, 1) 'TIMEX IS THE TIME WE ARE GOING TO INTERPOLATE
DATA1 = Mat(j - 2, 2)
DATA2 = Mat(j - 1, 2)
'THE LINEAR INTERPOLATION FORMULA
NewValue = ((DATA2 - DATA1) / (TIME2 - TIME1)) * ((TIMEX) - TIME1) + DATA1
Mat(j - 1, 2) = NewValue
'SINGLE X CELL FILLED
'SCANS FOR MORE XS
CHECK = 1
For z = 1 To NODES
    If Mat(z, 2) = "X" Then CHECK = 0
Next z
'THIS LOOP WILL CONTINUE AS LONG AS THERE ARE XS
Loop
'FINAL OUTPUT TO WORKSHEET
LinearInterpolation = Mat
End Function

[karrotman]

Since I am a new poster I will have to post 5 posts before I can show you my actual output.

[karrotman]

So here is 4th post

[karrotman]

And here is 5th. Now see original post for what I want.

[CatDaddy]

Im not sure which of those cells you are trying to highlight

[karrotman]

I am trying to highlight the cells that are given in the inputs. For example, if one of the given times is 5 with a value of 2, then in the final matrix I want the time 5, data 2 cells highlighted.