If cell Then multiple properties

[ThomasOES]

I use the code below to check the quality of a range of cells. If a quality is met I want the cell being checked to have a black background and white text. I can easily apply the code for a black background but I cant figure out how to add the property to have a white font. Do I need to repeat the entire If statement with Then cell.Font.Color = vbWhite? Or is there a more elegant way to apply multiple properties to a cell?

>Range("repsamps").Select
>For Each cell In Selection
>'IF SAMPLE IS HOMOGENOUS AT 95% THEN BLACK CELL WHITE TEXT
>If Abs(Application.Average(Range("repsamps")) - cell.Value) < _
>(Range("Lot95WVal") / 2) _
>Or Abs(Application.Average(Range("repsamps")) - cell.Value) = _
>(Range("Lot95WVal") / 2) _
>Then cell.Interior.Color = vbBlack

What I try and doesn't work is:

If Abs(Application.Average(Range("repsamps")) - cell.Value) < _
>(Range("Lot95WVal") / 2) _
>Or Abs(Application.Average(Range("repsamps")) - cell.Value) = _
>(Range("Lot95WVal") / 2) _
>Then cell.Interior.Color = vbBlack
>cell.Font.Color = vbWhite

>Next cell

It just colors all cells text white, doesn't apply the if parameters

Any help would be much appreciated.

Thanks

Tom

 

[Fluff]

Try

If Abs(Application.Average(Range("repsamps")) - cell.Value) <= (Range("Lot95WVal") / 2) Then
   cell.Interior.Color = vbBlack
Else
   cell.Font.Color = vbWhite
End If

 

[jproffer]

I'm not going to take all the ">" out of what you have just to copy the whole thing, but you can use "If..... <=....." and skip the "OR". If you want to change more than one thing, you may have to use a multi line IF statement.

If ........... <= cell.Value Then
    cell.Interior.Color = vbBlack
    cell.Font.Color = vbWhite
End If

 

[Fluff]

Misread the OP, my code should be

If Abs(Application.Average(Range("repsamps")) - cell.Value) <= (Range("Lot95WVal") / 2) Then
   cell.Interior.Color = vbBlack
   cell.Font.Color = vbWhite
End If

 

[Joe4]

To post readable code that we can copy without requiring us to edit and remove a bunch of characters, use Code Tags like the responders above did.
Read here on how to do that: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

 

[ThomasOES]

I read the link. Will do next time.

Tom

 

[ThomasOES]

Thanks MVP. Works like magic

Tom