Formula / VBA to identify highlighted cells.

[ukmike007]

Good morning all... hoping that someone can give me a formula / VBA that will identify lines that contain color highlighting please?

I have a workbook containing approx 50 worksheets, each containing between 4 and 200 lines and I am trying to consolidated data onto the first workbook based on rows that have had some lines highlighted cells within. Highlighted cells are all yellow.

I am all set to consolidate the data but need a function to identify which lines are highlighted yellow.

Thanks in advance

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Are the yellow cells in a particular column or can they be scattered across multiple columns.
If they are possibly in more than one column, can there be more then one yellow cell in the same row?

Are the highlights, or any of them, the result of Conditional Formatting or are they simply coloured directly.

 

[ukmike007]

Thanks Peter

I have updated my details, I am using Office 365.

There are yellow cells in approx 6 columns of rows that i need to extract data from, but the columns used vary according to other parameters, so I will need to either use a dynamic range to identify where to look for highlighting.

They have been manually highlighted using Fill Color not conditional formatting.

Regards

MNike

 

[Peter_SSs]

Thanks for updating your profile.

Next thing is a way to give us a better idea of what you data is actually like.:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

There are yellow cells in approx 6 columns of rows

So might a worksheet look like this?

ukmike007.xlsm
	A	B	C	D	E	F	G	H	I
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9
2	data	data	data	data	data	data	data	data	data
3	data	data	data	data	data	data	data	data	data
4	data	data	data	data	data	data	data	data	data
5	data	data	data	data	data	data	data	data	data
6	data	data	data	data	data	data	data	data	data
7	data	data	data	data	data	data	data	data	data
8	data	data	data	data	data	data	data	data	data
9	data	data	data	data	data	data	data	data	data
Sheet1

Or would it look more like this?

ukmike007.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10
2	data	data	data	data	data	data	data	data	data	data
3	data	data	data	data	data	data	data	data	data	data
4	data	data	data	data	data	data	data	data	data	data
5	data	data	data	data	data	data	data	data	data	data
6	data	data	data	data	data	data	data	data	data	data
7	data	data	data	data	data	data	data	data	data	data
8	data	data	data	data	data	data	data	data	data	data
9	data	data	data	data	data	data	data	data	data	data
Sheet1 (2)

Or might it be something different altogether?

Hard to put together a suggestion without being able to reasonably accurately build something like what you actually have.

 

[ukmike007]

It is like the 1st example. When I said about the highlighting moving, the same columns are used within the individual worksheets, lost worksheets would have highlighting in column K, but there are about 8 worksheets that the highlighting wouldn't start until column L or M. I would be happy to replicate formulae / VBA to account for this myself.

 

[bobsan42]

try this function, it should tell you if you have yellow fill in the selected range:

Function IsThereYellow(ref As Range, Optional ByVal clr = vbYellow) As Boolean
    Dim cc As Range, ret As Boolean
    Set ref = Intersect(ref, ref.Parent.UsedRange)
    If Not ref Is Nothing Then
        For Each cc In ref.Cells
            ret = cc.Interior.Color = clr
            If ret Then Exit For
        Next cc
        IsThereYellow = ret
    End If
    Set cc = Nothing
End Function

so you can use it as a formula like this:

=IsThereYellow(Sheet4!1:1)

However, this will not return TRUE if yellow fill is a result from Conditional formatting.

 

[bobsan42]

try this function, it should tell you if you have yellow fill in the selected range:

Just a word of warning - it won't recalculate automatically.

 

[ukmike007]

Thanks Bobsan - that worked great.

 

[Peter_SSs]

It is like the 1st example.

In that case, although I see that you have something that you can work with, I'm offering an alternative anyway.
For the moment I have assumed

• that all the sheets have data in column A for the extent of data on that sheet
• there is a 'Master' sheet where the yellow rows are to go.
• each sheet has a header row that does not need to be copied to 'Master'

If any of the assumptions is incorrect, should be able to adapt.

With a copy of your workbook, see if you think this might be some use.

Sub GetYellowRows()
  Dim ws As Worksheet, wsMaster As Worksheet
  Dim rFound As Range
  
  Set wsMaster = Sheets("Master")
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  Application.ScreenUpdating = False
  For Each ws In Worksheets
    If ws.Name <> "Master" Then
      With ws
        Set rFound = .Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchFormat:=True)
        If Not rFound Is Nothing Then
          With Intersect(.UsedRange, rFound.EntireColumn)
            .AutoFilter Field:=1, Criteria1:=vbYellow, Operator:=xlFilterCellColor
            .Offset(1).EntireRow.Copy Destination:=wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1)
          End With
          .AutoFilterMode = False
        End If
      End With
    End If
  Next ws
  Application.FindFormat.Clear
  Application.ScreenUpdating = True
End Sub