Fill Cell with Colour if Q
 

Is there VBA code that will fill a cell with a certain colour, say yellow, if another cell = value of any one of 5,6,100,175,101 etc

I have the array list in a Range (called 'Products) and I want to easily see these products on a Sales mix report?

So Column E contains the Product Number; Col F Contains Product Name, which I want to fill with a colour, and Col M contains Qty Sold, which I'd like also to fill with a colour

Fill Cell with Colour if Q
 

Hi Sean,

Am Thu, 17 Nov 2016 07:22:08 -0800 (PST) schrieb :

Is there VBA code that will fill a cell with a certain colour, say yellow, if another cell = value of any one of 5,6,100,175,101 etc

I have the array list in a Range (called 'Products) and I want to easily see these products on a Sales mix report?

So Column E contains the Product Number; Col F Contains Product Name, which I want to fill with a colour, and Col M contains Qty Sold, which I'd like also to fill with a colour


try:

Sub FillColor()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For Each rngC In .Range("E2:E" & LRow)
If Application.CountIf(Range("Products"), rngC) 0 Then
rngC.Offset(, 1).Interior.Color = vbYellow
rngC.Offset(, 8).Interior.Color = vbYellow
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Fill Cell with Colour if Q
 

Fantastic Claus (again) works like a dream

Fill Cell with Colour if Q
 

Hi Sean,

Am Thu, 17 Nov 2016 07:47:16 -0800 (PST) schrieb :

Fantastic Claus (again) works like a dream

if you have a huge amount of rows try:

Sub FillColor2()
Dim LRow As Long, i As Long
Dim varCheck As Variant, varData As Variant
Dim strCheck As String

varCheck = Range("Products")
With Application
strCheck = Join(.Index(.Transpose(varCheck), 1, 0), ",")
End With

With ActiveSheet
LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
varData = .Range("E1:E" & LRow)
For i = LBound(varData) To UBound(varData)
If InStr(strCheck, varData(i, 1) & ",") Then
.Cells(i, "F").Interior.Color = vbYellow
.Cells(i, "M").Interior.Color = vbYellow
End If
Next
End With
End Sub

This macro is a little bit faster


Regards
Claus B.
--
Windows10
Office 2016

Fill Cell with Colour if Q
 

Thanks Claus, I'll have a look at that one, although with 2,500 rows the previous one did it in the blink of an eye

Xem thêm
_________________
bulong

https://t.co/J2X3EPlKni

Đang cần gấp
_________________
fetish porn

https://t.co/61qhR2WWSE