Copy value from cell based on colour

Sophia Hickman

New Member
Joined
Jun 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have the following sheet, to be used for invoicing purposes.
Our client has requested us to colour in each box that we are applying for that month, however there is no tally or record of total monies claimed for in that month.

I am hoping someone can help me, for each cell that i colour in yellow as below - i would like this value to populate on another tab/sheet

Plot 1Plot 1Plot 2Plot 2Plot 3Plot 3Plot 4Plot 4
DescriptionGarageGarageGarageGarage
PrivatePrivatePrivatePrivatePrivatePrivatePrivatePrivate
1st Fix£2,034.75£82.80£1,898.85£82.80£1,898.85£82.80£2,370.90£82.80
2nd Fix£2,110.50£124.20£1,959.50£124.20£1,959.50£124.20£2,484.00£124.20
Finals£192.75£177.65£177.65£230.10
Door Entry
A.O.V
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Hey Sophia-

Please try something like this:

VBA Code:
Sub Test()

Dim rng As Range
Dim cll As Range
Dim tgtSht As Worksheet
Dim intCnt As Integer

Set rng = Sheets("Sheet1").Range("A1:I9") 'define range to loop through...i.e. this is where you define what range to look through
Set tgtSht = Sheets.Add 'Add New Worksheet to put values

'Loop through each cell in the range and identify if it is "Yellow"...
For Each cll In rng
    If cll.Font.Color = 6609655 Then
        'If the cell is "Yellow" move the value to another worksheet
        tgtSht.Cells(intCnt + 1, 1) = cll.Value
        intCnt = intCnt + 1 'increment counter in order to move to the next available row
    End If
Next cll

End Sub
 

Sophia Hickman

New Member
Joined
Jun 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hey Sophia-

Please try something like this:

VBA Code:
Sub Test()

Dim rng As Range
Dim cll As Range
Dim tgtSht As Worksheet
Dim intCnt As Integer

Set rng = Sheets("Sheet1").Range("A1:I9") 'define range to loop through...i.e. this is where you define what range to look through
Set tgtSht = Sheets.Add 'Add New Worksheet to put values

'Loop through each cell in the range and identify if it is "Yellow"...
For Each cll In rng
    If cll.Font.Color = 6609655 Then
        'If the cell is "Yellow" move the value to another worksheet
        tgtSht.Cells(intCnt + 1, 1) = cll.Value
        intCnt = intCnt + 1 'increment counter in order to move to the next available row
    End If
Next cll

End Sub
Thank you Matt, I have very basic excel knowledge, when i have written the below it is saying there is a bug! I have highlighted the row that it doesnt like below, any help would be great.
basically i am looking to duplicate the payment schedule sheet on another tab (we total sheet) but only when i colour in the cell on the payment sheet.

Sub Test()

Dim rng As Range
Dim cll As Range
Dim tgtSht As Worksheet
Dim intCnt As Integer

Set rng = Sheets("DURK-BENG PAYMENT SCHEDULE").Range("B12:P71")
Set tgtSht = Sheets("WE TOTAL SHEET").Add

For Each cll In rng
If cll.Font.Color = 6609655 Then
tgtSht.Cells(intCnt + 1, 1) = cll.Value
intCnt = intCnt + 1
End If
Next cll

End Sub
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Please try the code with a few modifications:

VBA Code:
Sub Test()

Dim rng As Range
Dim cll As Range
Dim intCnt As Integer

Set rng = Sheets("DURK-BENG PAYMENT SCHEDULE").Range("A1:I9") 'define range to loop through...i.e. this is where you define what range to look through
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "WE TOTAL SHEET" 'Add New Worksheet named WE TOTAL SHEET to put values

'Loop through each cell in the range and identify if it is "Yellow"...
For Each cll In rng
    If cll.Font.Color = 6609655 Then
        'If the cell is "Yellow" move the value to another worksheet
        Cells(intCnt + 1, 1) = cll.Value
        intCnt = intCnt + 1 'increment counter in order to move to the next available row
    End If
Next cll

End Sub
 

Sophia Hickman

New Member
Joined
Jun 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Please try the code with a few modifications:

VBA Code:
Sub Test()

Dim rng As Range
Dim cll As Range
Dim intCnt As Integer

Set rng = Sheets("DURK-BENG PAYMENT SCHEDULE").Range("A1:I9") 'define range to loop through...i.e. this is where you define what range to look through
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "WE TOTAL SHEET" 'Add New Worksheet named WE TOTAL SHEET to put values

'Loop through each cell in the range and identify if it is "Yellow"...
For Each cll In rng
    If cll.Font.Color = 6609655 Then
        'If the cell is "Yellow" move the value to another worksheet
        Cells(intCnt + 1, 1) = cll.Value
        intCnt = intCnt + 1 'increment counter in order to move to the next available row
    End If
Next cll

End Sub
Thanks Matt

This now opens a new tab named WE TOTAL SHEET

However when i press run, it doesnt pull through any figures

Have tried changing the colour to 6609655 (R:100, G:218, B:247) but still not working

Any other ideas please? I feel its super close now but not quite there
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
When I look at the color based on your post I have:

Red: 247 ; Green: 218 ; Blue: 100

If you highlight a cell with the coloring in it can you run this macro and validate the font color?

VBA Code:
Sub GetColor()
   MsgBox ActiveCell.Font.Color
End Sub

Is the cell actually colored or is this conditional formatting?
 

Forum statistics

Threads
1,136,323
Messages
5,675,091
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top