0

Here is my code

 If Range("E" & i).Value > Range("C" & i).Value * 0.2 Then Range("E" & i).Interior.Color = 13619199

This line of code is meant to highlight all cells in column E if column E is 20% greater than column C. The code works. However, I want it to highlight the absolute value of E as well because I have some negative values in there as well. How do I get this line of code to take into account the negative values?

I appreciate all answers in advance.

Thanks,

G

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GCC
  • 285
  • 6
  • 23
  • `Abs(Range("E" & i).Value)` returns the absolute value of the cell. Why aren't you using a conditional formatting rule? –  Nov 24 '17 at 18:59
  • Will conditional formatting speed up my macro? Also, I have a few tables of varying lengths. Will conditional formatting be able to take into account the various lengths? I am fairly new to VBA – GCC Nov 24 '17 at 19:01
  • 1
    Conditional formatting removes the need for VBA altogether and yes, it can be made to compensate for either structured tables or data matrices. –  Nov 24 '17 at 19:03
  • It's amazing to me how often I see people ignoring features built-in to Office and instead decide to start over from scratch in VBA, as if they can do a better job of it in VBA than Microsoft could. To be fair, it's likely an issue of ignorance to what features are available, and so when they hit a wall, _"well I guess it's time to learn VBA"_... I've been guilty of it myself, the most recent was a realization that I've been dragging around my `RevInStr` function for 20 years for no reason... lol – ashleedawg Nov 24 '17 at 19:52

1 Answers1

1

This wasn't asked in your original question but your comments give the impression that you're unfamiliar with Conditional Formatting? This is the simplest example of what it can do (better than and quick code samples we can give you!)...

Conditional Formatting 1

Conditional Formatting 2

There are many other ways conditional formatting can be used, some trickier than others to setup, but all with plenty of examples available online. A commonly asked one is how to highlight an entire how based on the value of one cell.

.

Something awesome made from Conditional Formatting:

Lastly, on the topic of Conditional Formatting being cooler than it sounds, a mathematician took it to a whole new level with a Conditional Formatting-based JPG-to-Excel convertor that's worth checking out!

.

PIXEL

ashleedawg
  • 20,365
  • 9
  • 72
  • 105