Peter_Parker
New Member
- Joined
- Sep 13, 2012
- Messages
- 5
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.
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.