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
-4105 when I point it to M14, but again, I don't have any red font; I only have red fill, which is what M14 is (via conditional formatting)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Oh you have red fill only...

Tell me what this returns
VBA Code:
Sub GetColorIndex()
    MsgBox Range("M14").DisplayFormat.Interior.ColorIndex
End Sub

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Change 2 code lines to
'Get the ColorIndex used in CF
lMyColorIndex = 3

and below

If rRow.Cells(1, i).DisplayFormat.Interior.ColorIndex = lMyColorIndex Then
dic(rValues.Cells(1, i).Value) = ""
End If


M.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793

ADVERTISEMENT

Now getting a 1004 error on this line:

wsResult.Range("B" & j).Resize(1, dic.Count) = dic.keys

Here's my full code:

VBA Code:
Sub aTest()
    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 55 '<-- 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
            j = j + 1
            wsResult.Range("A" & j) = sProject
            wsResult.Range("B" & j).Resize(1, dic.Count) = dic.keys
        Next rRow
    End With
     
End Sub
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Is there a project with no cells highlighted?
If so, replace the lines at the end of the code to ...

VBA Code:
  'Display Result in Sheet2
            j = j + 1
            wsResult.Range("A" & j) = sProject
            If dic.Count > 0 Then wsResult.Range("B" & j).Resize(1, dic.Count) = dic.keys
        Next rRow

M.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793

ADVERTISEMENT

Hi Marcelo,

Thank you again. This mostly works now!

The only thing that's not quite right is it is showing all projects, even when there are no red cells. Is there a little tweak to only show the projects with at least 1 red cell?

Thank you so much for your help and patience!
 

Marcelo Branco

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

Change the last bit of code (see post 15 above) to
VBA Code:
'Display Result in Sheet2
If dic.Count > 0 Then
    j = j + 1
    wsResult.Range("A" & j) = sProject
    wsResult.Range("B" & j).Resize(1, dic.Count) = dic.keys
End If

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,152
Messages
5,640,423
Members
417,142
Latest member
andygame

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