Cell must return last painted cell colour and content

[Danikuku]

Hi guys,

Let me try to explain my problem:

	A
1	20 (red background)
2	40
3	30 (yellow background)
...
10	30 (yellow background)
11	XPTO

<tbody>
</tbody>

In cell A1 I have a value with a red background colour, in A2 I have a value with no background colour and in cell A3 I have again a value with a Yellow background colour and so on, with different colours and values (non conditional formatting).

I have two questions:

1. In Line 10 I want to have the final result.
This means I want it to give me the last coloured cell. In this case it should present the colour and value of A3, so I want it to be 30 and painted of Yellow. Do you know how to do this automatically? This "final result" is always in line 10.


2. Each background colour represents a brand. In line 11, I wanted it to write the brand "XPTO", so I didn't need to be checking it manually.
I have another table that states the brands:

colour	Brand
yellow	XPTO
Red	ZZBVC

<tbody>
</tbody>

Thank you!

 

[Special-K99]

I believe VBA is your only solution to this.

 

[Mike LH]

Hi,

You haven't given enough information to work out where we get Brand etc from but this code populates A10 with the colour and value. Select the cells you want to test and run the code.

Sub getColor()
Dim x As Long
For x = Selection.Cells.Count To 1 Step -1
If Selection.Cells(x).Interior.Color <> 16777215 And _
    Selection.Cells(x).Value <> "" Then
    With Cells(10, "A")
        .Interior.Color = Selection.Cells(x).Interior.Color
        .Value = Selection.Cells(x).Value
    End With
    Exit Sub
End If
Next

End Sub

 

[Danikuku]

Hi Mike,

So first, thank you for the help. Your code is specifically for A10 and I need it to work on B10, C10, until LP10! Can you tell me how to do that? Also, you are an expert on this, but I don't really understand VB, so I went ALT+F11 and in the sheet I want this to run "sheet 2" I pasted the code. Then, when you say "Select the cells you want to test and run the code". How do I run the code? It's different than a function or a macro right? I am sorry, but I am really in the beggining.

Also, you are saying it is lacking information. Let me try to better explain:

In A38: B60 I have a matrix with the colour meaning, where I stated the colour and brand (I said colour, but I can be done with colour indexes, I think?):

Colour	Brand
Cell painted in yellow	XPTO
Cell painted in red	ZZBVC
Cell painted in Blue	XXYYZ

<tbody>
</tbody>

In line 11, lets say A11, I wanted to read the colour in A10 and being yellow, he would wright "XPTO".

Hi,

You haven't given enough information to work out where we get Brand etc from but this code populates A10 with the colour and value. Select the cells you want to test and run the code.

Sub getColor()
Dim x As Long
For x = Selection.Cells.Count To 1 Step -1
If Selection.Cells(x).Interior.Color <> 16777215 And _
    Selection.Cells(x).Value <> "" Then
    With Cells(10, "A")
        .Interior.Color = Selection.Cells(x).Interior.Color
        .Value = Selection.Cells(x).Value
    End With
    Exit Sub
End If
Next

End Sub

 

[Mike LH]

Hi,

There are practical difficulties with what you want to do regarding looking up the colour but let's deal with the easy bits first, the code. The version below now works on any column and puts the colour and value of the required cell into row 10 of that column. To use the code you do this.

ALT+F11 to open vb editor and delete the last code you pasted in, Then right click 'ThisWorkbook' and insert module and paste the code into that newly created module and then close VB editor. Now to run the code you do this:-

1. Select the range of cells to evaluate.
2. Click on the VIEW tab and click on macros and in the dropdown select 'ThisWorkbook' and you should see the name of the macro.
3. Highlight the name and click run.

We can make the running of the macro much simpler by creating a shortcut. Repeat steps 1 and 2 above and at step 3 highlight the macro name and click OPTIONS and in the input box enter a letter and I suggest you hold down the shift key for an uppercase K and click the red x to exit. Now to run the macro you select the cells to evaluate and hold down CTRL+Shift and tap the K key.


Now it gets more difficult because nowhere in the vba model does it return the colour of the cell so we can't lookup Yellow and return your text. There is something that is called a colour constant but I think that in this case that probably isn't going to help much so here's what I suggest. In the range A38: B60 colour the cells with the colours you're actually using in the ranges so each row is a different colour. The code below will now test the colours in that range and return the text from column B

Sub getColor1()
Dim x As Long, c As Range
For x = Selection.Cells.Count To 1 Step -1
If Selection.Cells(x).Interior.Color <> 16777215 And _
    Selection.Cells(x).Value <> "" Then
    With Cells(10, Selection.Column)
        .Interior.Color = Selection.Cells(x).Interior.Color
        .Value = Selection.Cells(x).Value
    End With
    GoTo getmeout
End If
Next
getmeout:
For Each c In Range("B38:B60")
    If c.Interior.Color = Cells(10, Selection.Column).Interior.Color Then
        Cells(11, Selection.Column) = c.Value
    End If
Next
End Sub

 

[Danikuku]

Dear Mike,

Thank you for your patience.
I can live without that last hard part.

But there is just one thing! The macro, isn't working for a range of cells, but only for a cell.

What I mean to say is that selecting the range K35:R35, and run the macro, it is only filling cell K36 (based on K35). So I have to select L35, run the macro and do it all over again for M, N, ....
I was hopping I could select a range, run the macro and that's it!

Also, Is there a way of this not being a macro, but a function, for example? The intention was not need to run the macro, but do it automatically!


Thank's again!

Hi,

There are practical difficulties with what you want to do regarding looking up the colour but let's deal with the easy bits first, the code. The version below now works on any column and puts the colour and value of the required cell into row 10 of that column. To use the code you do this.

ALT+F11 to open vb editor and delete the last code you pasted in, Then right click 'ThisWorkbook' and insert module and paste the code into that newly created module and then close VB editor. Now to run the code you do this:-

1. Select the range of cells to evaluate.
2. Click on the VIEW tab and click on macros and in the dropdown select 'ThisWorkbook' and you should see the name of the macro.
3. Highlight the name and click run.

We can make the running of the macro much simpler by creating a shortcut. Repeat steps 1 and 2 above and at step 3 highlight the macro name and click OPTIONS and in the input box enter a letter and I suggest you hold down the shift key for an uppercase K and click the red x to exit. Now to run the macro you select the cells to evaluate and hold down CTRL+Shift and tap the K key.


Now it gets more difficult because nowhere in the vba model does it return the colour of the cell so we can't lookup Yellow and return your text. There is something that is called a colour constant but I think that in this case that probably isn't going to help much so here's what I suggest. In the range A38: B60 colour the cells with the colours you're actually using in the ranges so each row is a different colour. The code below will now test the colours in that range and return the text from column B

Sub getColor1()
Dim x As Long, c As Range
For x = Selection.Cells.Count To 1 Step -1
If Selection.Cells(x).Interior.Color <> 16777215 And _
    Selection.Cells(x).Value <> "" Then
    With Cells(10, Selection.Column)
        .Interior.Color = Selection.Cells(x).Interior.Color
        .Value = Selection.Cells(x).Value
    End With
    GoTo getmeout
End If
Next
getmeout:
For Each c In Range("B38:B60")
    If c.Interior.Color = Cells(10, Selection.Column).Interior.Color Then
        Cells(11, Selection.Column) = c.Value
    End If
Next
End Sub