If Condition-turn cell red

[Jimmylaki]

Hello,

I am writing a code which will check if price in column L is lower than column Y or greater than column Z. The following is aimed to loop through the ranges and turn the interior of cells in L red if the condition is false. If I could have some tips on how to re write the code for the IF condition which was recorded in excel and to make it fire. This is the snippet of my code:

For Each D In Sheets("Deals").Range("L1:L" & LASTROW).Cells
If D.Value = "R[-25]C[-1]<R[-25]C[13],IF(R[-25]C[-1]<R[-25]C[13]),IF(R[-25]C[-1]>R[-25]C[14],IF(R[-25]C[-1]>R[-25]C[14])))" Then
ActiveCell.interior.ColorIndex = 6

End If

Next D

 

[HarleyQuinn]

You could just use conditional formatting setting the condition that the cell value is not between Y and Z.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Jimmylaki]

Hello HarleyQuinn,

The intended code is for a bigger macro which calls a file saved into a share drive. the vba code opens the file manipulates the data and saves to a new location. because the file changes daily i am using the condition in vba. if i used conditional formatting in the file it would be lost everyday. is this correct?

 

[HarleyQuinn]

I shouldn't think the formatting would be lost if you're saving the file as another filename after manipulating data.

Worst case you can set the conditional formatting in the VBA code. It's alot nicer than the formula you're trying to apply in my opinion.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[SkipVought]

Ditto on Condotional Formatting. Turn on your macro recorder if you need VBA.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oh, and as a matter of practice, whether CF or a formula, you don't need a loop to apply individually, to a range of cells.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Jimmylaki]

The original thread was a result of the macro recorder but regardless its resolved.