How to highlight cells when using Excel VBA conditional formatting?

[Peter_Parker]

Okay so I am stuck and would appreciate any help or advice at all.
I am trying to highlight the cells that meet two conditions. That is to say, in this particular case if the absolute value in a cell is greater than 3 and this same absolute value is greater than another value in an adjacent cell then the cell gets highlighted. So for example, my data looks like this:
-1.2, 1.71, 6.26, 10.2, 3.3, 0.4 4 -4
So if the absolute values of any of the six values on the left are greater than 3 and also greater than four (the adjacent four on the right) then they get highlighted. The adjacent value will change and not always be four. I need to do this in VBA and I want the code to work in such a way that when I move to another line of data similar to the one above the two conditions are tested and the correct values are highlighted. So this is what I have written so far in VBA.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ABS(RC) >3,ABS(RC) >RC[9])"
Selection.FormatConditions(1).Interior.ColorIndex = 45</code>The problem:
I am using the R1C1 format as I want it to update automatically when I apply this code to another row of data. But currently this code only highlights the number 10.2 and not the 6.26 as well as it should. I think that if I leave the code as ABS(RC) this will refer to the current cell value? Please advise. Also the number 4 (adjacent cell) is nine columns from the first value -1.2 - hence me writing RC[9].

So just to try and be clear again, The reference for the second condition is also relative (not fixed) 2) I won't have the cellcontaining the second condition highlighted . What I want is for the cells that I have selected to be highlighted if the absolute value of that cell is greater than the value of the second condition (and greater than 3 - condition 1). In this case I want to see 6.26 and 10.2 highlighted as they are greater than 3 and greater than 4. For some reason this is just not happening.

 

[Max1616]

For your second condition, is that number always the last column in the row? I'm confused at how you know 4 is the 2nd condition in your example for both 6.26 and 10.2. Also is there a reason you are using conditional formatting vs. just highlighting the cell?

I would use something like this:

Sub PeterParker()
Dim lcolumn As Integer
Dim MyRow As Long
Dim i As Long

MyRow = ActiveCell.Row
lcolumn = Cells(MyRow, Columns.Count).End(xlToLeft).Column

For i = 1 To lcolumn - 1
    If Abs(Cells(MyRow, i)) > 3 And Abs(Cells(MyRow, i)) > Cells(MyRow, lcolumn) Then Cells(MyRow, i).Interior.ColorIndex = 6
Next i

End Sub

It will check every cell in the row you have selected and check it against the number 3 and the last value within that column.

You could write a loop to have it move to every row in your data set. I would reccomend posting a more detailed explanation in the future or posting an example workbook with an example of what you are looking for.

Sincerely,

Max