Code highlights additional activecell in addition to what it has to highlight

Thank you very much.. That worked....

Thanks

-Subbby

Thanks for the advise to !

-Subbby

Originally Posted by LJMetzger

Hi subby,



a. By eliminating 'Select', the other cell was not highlighted.
b. When you had if ... then on the same line, Excel thought the if statement was completed.
It takes a log more space, but I almost always use the following if construction:

If a = b then
  c = d
Endif

Lewis

Just an add on requirement.

I do have to change interior color index of ALL CELLS with a value in it under the r.value cell. I used

Range(r.value, Range("k65536").End(xlUp))

Giving me errors.

Any idea on that ?

Hi,

Your syntax is close, but just not quite right. You need Range(Range1,Range2), but You have Range(Value,Range2).

Try:

Sub FindMax()
  'MAX is an Excel KEYWORD.  It is NOT RECOMMENDED to use Excel KEYWORDS as variables or function/Sub names.

  Dim myRange As Range
  Dim r As Range
  
  Dim rw5 As Long

  For rw5 = 17 To 46
  
    Set r = Cells(rw5, 11)

    If r.Value - Cells(7, 6) <= 20 And r.Value - Cells(7, 6) > 0 Then
      ''''' r.Interior.ColorIndex = 6   'This NOT NEEDED NOW - the code below will set the Color of this cell
      r.Font.Color = vbBlack
      r.Font.Bold = True
      
      'I do have to change interior color index of ALL CELLS with a value in it under the r.value cell
      'I used Range(r.value, Range("k65536").End(xlUp))
      '
      'r is a Range Object.
      'r.Value is the Value of the Range object
      '
      'Find all cells with values in the Column below the Cell referred to by 'r'.
      'Set the ColorIndex of all values in the Range
      Set myRange = Range(r, Range("k65536").End(xlUp))
      myRange.Interior.ColorIndex = 6  
      
    End If

  Next rw5

  'Clear object pointers
  Set r = Nothing
  Set myRange = Nothing

End Sub

It seems like you may want to exit the loop, when you find your match. To do this you can use:

Exit For

Lewis