Interior color question

[Cowichandave]

Want to show only the value only if equal to F8 else it should be blank until another cell has the same value as F8
Without the if statement it works but shows value of each cell in the range
With the if statement it is blank or whatever value I choose
What am I missing?


Range("F8:F2000").Select
For Each c In Selection
c.Offset(0, -4).Value = c.Interior.Color

If c.Offset(0, -4).Value <> Range("F8").Value Then c.Offset(0, -4).Value = ""
Next c

 

[PeteWright]

Hi @Cowichandave

I don't understand, what your code should do.
Could you provide an example?
Maybe a screenshot of a (manually) filled table with the desired results.

 

[Cowichandave]

Hi @Cowichandave

I don't understand, what your code should do.
Could you provide an example?
Maybe a screenshot of a (manually) filled table with the desired results.

All I want to is to display value of the color cell if it is14348258 or whatever color i want to show the value of.
I do not want it to show any other color value

 

[PeteWright]

Okay, one more question though:

What is the "reference" cell with your desired color, where the Macro should look at?
May it Be let's say A1 or F1? Or how does the code know which color you're after?

 

[Cowichandave]

Okay, one more question though:

What is the "reference" cell with your desired color, where the Macro should look at?
May it Be let's say A1 or F1? Or how does the code know which color you're after?

My formula selects range F8 to F2000. F8 always contains the color I want.
The results are to be listed in the offset which is Range B But I only want the results in the range to be listed if they equal the value of F8 otherwise the result cells should be blank if not equal to the value of F8. Hope that clears it up. My formula without the if statement just listed every value. Its the if statement that I could not make work. Hope you have a better solution

 

[PeteWright]

This should do it:

Range("F8:F2000").Select
  For Each c In Selection
    If c.Interior.Color = Range("F8").Interior.Color Then c.Offset(0, -4).Value = c.Interior.Color
Next c

 

[Cowichandave]

This should do it:

Range("F8:F2000").Select
  For Each c In Selection
    If c.Interior.Color = Range("F8").Interior.Color Then c.Offset(0, -4).Value = c.Interior.Color
Next c

AWESOME. That works. The results show up in column B. Now 1 more question. How would I auto increment that in column c. The first value from column B would become a 1 in Column C. The next matching value would become a 2 and so on until the end of the range

 

[PeteWright]

Alright, that will slightly change the code:

Public Sub InteriorColorValue()
   Dim c As Range
   Dim i As Long: i = 1
   Range("F8:F2000").Select
   For Each c In Selection
      If c.Interior.Color = Range("F8").Interior.Color Then
         c.Offset(0, -4).Value = c.Interior.Color
         c.Offset(0, -3).Value = i: i = i + 1
      End If
   Next c
End Sub

I've put it in a separate sub procedure for testing, feel free to extract the code and paste it in your macro

   Dim c As Range
   Dim i As Long: i = 1
   Range("F8:F2000").Select
   For Each c In Selection
      If c.Interior.Color = Range("F8").Interior.Color Then
         c.Offset(0, -4).Value = c.Interior.Color
         c.Offset(0, -3).Value = i: i = i + 1
      End If
   Next c

 

[Cowichandave]

Alright, that will slightly change the code:

Public Sub InteriorColorValue()
   Dim c As Range
   Dim i As Long: i = 1
   Range("F8:F2000").Select
   For Each c In Selection
      If c.Interior.Color = Range("F8").Interior.Color Then
         c.Offset(0, -4).Value = c.Interior.Color
         c.Offset(0, -3).Value = i: i = i + 1
      End If
   Next c
End Sub

I've put it in a separate sub procedure for testing, feel free to extract the code and paste it in your macro

   Dim c As Range
   Dim i As Long: i = 1
   Range("F8:F2000").Select
   For Each c In Selection
      If c.Interior.Color = Range("F8").Interior.Color Then
         c.Offset(0, -4).Value = c.Interior.Color
         c.Offset(0, -3).Value = i: i = i + 1
      End If
   Next c

That is absolutely awesome. Thanks I will mark this as solved

 

[PeteWright]

Thanks, glad I could help