Show Values of Only Cells that are Highlighted?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Hi lovely people! Hope everyone is doing well today.

I have a list of Project Names in A2:A100. In D2:BC100 there is a value of either Y or N. Some of the N cells are highlighted red and some of the Y cells are highlight yellow based on conditional formatting, but for this purpose, I only care about cells that are both N and red.

I want to create a summary of this worksheet that is easier to read. Essentially, I want to list only the projects that have at least one red N on their row, and then only list the value from the column header (row 1) for where there's a red N.

Here is a mock up of how it currently looks:

Example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBC
1ProjectCountryCity12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
2Project 1NNNNNNNNYYYYNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYYNNYY
3Project 2NNNNYNNNNYNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNYNNYY
4Project 3NNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
5Project 4NNNNNNNNYYYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYN
6Project 5NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
Mock-Up


This is what I'm trying to achieve:

Example.xlsx
ABCDE
1Project 118272933
2Project 2624
3Project 318
4Project 41252
Sheet5


Is this possible, or is the conditional formatting a roadblock (vs normal highlighting?)

Using Office 365 ProPlus, and happy to use VBA as a solution if needed.

Thank you!
 
Last edited:

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Just when you thought you were done with me..... :)

I think I need two more things

1. I need to clear the data before starting. Added to code and seems to be working.

2. I realized I should also copy the respective value from column C (City in my mockup) of the Orders per Company Code worksheet to column B of the Summary worksheet. I've moved the other data over one column to start at column C and that is working, but I keep breaking things when trying to get the City values in here. Could I trouble you for help one last time?

VBA Code:
Sub aTest()

Dim ws As Worksheet
Set ws = Sheets("Summary")
ws.Cells.Clear

    Dim dic As Object, rData As Range
    Dim rRow As Range, i As Long, j As Long, rValues As Range
    Dim lMyColorIndex As Long, sProject As String
    Dim wsData As Worksheet, wsResult As Worksheet
  
    Set wsData = ThisWorkbook.Sheets("Orders per Company Code") 'assumnes data in sheet1
    Set wsResult = ThisWorkbook.Sheets("Summary") 'assumes there is a sheet named Sheet2
   
    j = 1
    With wsData
        'Get the ColorIndex used in CF
        lMyColorIndex = 3
      
        Set rData = .Range("A2:BC100") '<--adjust the range
        Set rValues = .Range("A1:BC1") '<--adjust the range
      
        For Each rRow In rData.Rows
            sProject = rRow.Cells(1, 1)
            Set dic = CreateObject("Scripting.Dictionary")
            For i = 4 To 51 '<-- adjust 9 to your last column (56 if it's BD)
                If rRow.Cells(1, i).DisplayFormat.Interior.ColorIndex = lMyColorIndex Then
                    dic(rValues.Cells(1, i).Value) = ""
                End If
            Next i
          
'Display Result in Sheet2
If dic.Count > 0 Then
    j = j + 1
    wsResult.Range("A" & j) = sProject
    wsResult.Range("C" & j).Resize(1, dic.Count) = dic.keys
End If
        Next rRow
    End With
      
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Please clarify

1.So do you want the output in Summary like this?
Pasta1
ABCDEF
1Project 1City118272933
2Project 2City2624
3Project 3Ciyt318
4Project 4Ciyt41252
Planilha2


2. I'm not sure what you meant by: " I've moved the other data over one column to start at column C and that is working,..."
The cities' names are at column C of wsData, aren't they?

M.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Yes, exactly like that. I just meant that I changed this line and changed the cell reference from B to C to give me an empty column: wsResult.Range("C" & j).Resize(1, dic.Count) = dic.keys
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Add a new variable

Dim lMyColorIndex As Long, sProject As String, sCity As String

Change code to get the City name
VBA Code:
For Each rRow In rData.Rows
            sProject = rRow.Cells(1, 1)
            sCity = rRow.Cells(1, 3)

Change also
Code:
'Display Result in Sheet2
If dic.Count > 0 Then
    j = j + 1
    wsResult.Range("A" & j) = sProject
    wsResult.Range("B" & j) = sCity
    wsResult.Range("C" & j).Resize(1, dic.Count) = dic.keys
End If

M.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,138
Messages
5,640,337
Members
417,139
Latest member
madcabbie

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