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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,528
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What was the criterion used in Conditional Formatting for a red N?

M.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Hi Marcelo,

The conditional formatting is actually just in every 2nd column, so those are actually the only ones that can be highlighted, and my mock up isn't perfect.

What it's doing is column E is comparing to D, column G is comparing to F, and so on. For E2, the conditional formatting is:

=AND(D2="N",E2="Y") for Yellow
Cell Value <> D2 for Red
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,528
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I created a macro and did a test with a small data sample - used a different CF formula for test purposes

Sheet Data
Teste 25032021 CF.xlsm
ABCDEFGHI
1ProjectCountryCity123456
2Project 1NNNNNN
3Project 2NNNNYN
4Project 3NNNNNN
5Project 4NNNNNN
6Project 5NNNNNN
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:I6Expression=E(D2="N";OU(D$1=3;D$1=5))textNO


Results in Sheet2
Teste 25032021 CF.xlsm
ABC
1
2Project 135
3Project 23
4Project 335
5Project 435
6Project 535
Sheet2


Try to adapt this macro to your real case

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("Sheet1") 'assumnes data in sheet1
    Set wsResult = ThisWorkbook.Sheets("Sheet2") 'assumes there is a sheet named Sheet2
    
    j = 1
    With wsData
        'Get the ColorIndex used in CF
        lMyColorIndex = .Range("H2").DisplayFormat.Font.ColorIndex '<--adjust H2 to a cell with red N
        
        Set rData = .Range("A2:I6") '<--adjust the range
        Set rValues = .Range("A1:H1") '<--adjust the range
        
        For Each rRow In rData.Rows
            sProject = rRow.Cells(1, 1)
            Set dic = CreateObject("Scripting.Dictionary")
            For i = 4 To 9 '<-- adjust 9 to your last column (56 if it's BD)
                If rRow.Cells(1, i).DisplayFormat.Font.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

Hope this helps

M.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793

ADVERTISEMENT

Hi Marcelo,

Thank you so much for the reply. I think I'm almost there.

I believe I need to change this part: DisplayFormat.Font.ColorIndex to something like DisplayFormat.CellColor.Interior.ColorIndex for highlighting instead of font color? I am not quite sure what the code is for it, as that doesn't work. Can you help?

Thank you!
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Just for a little more info, below is how I adjusted my code. I didn't make any changes to my Conditional Formatting (I don't think you intended me to?), but I did verify the colors match: 255,0,0 for RGB. Essentially, what it's doing is it's displaying every single value from row 1 for every single project. Did I do anything wrong, or is it just the cell color vs font color thing I mentioned above? Thank you.

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 = .Range("CA1").DisplayFormat.Font.ColorIndex '<--adjust H2 to a cell with red N
       
        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.Font.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,528
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Why did you use CA1 in that line?
lMyColorIndex = .Range("CA1").DisplayFormat.Font.ColorIndex '<--adjust H2 to a cell with red N

Instead of CA1 it should be a cell where the N has turned red due to the conditional formatting.

What format exactly did you use in conditional formatting? I assumed it was just Font Color -> red

M.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Hi Marcelo,

CA1 was a cell that I entered an N in and manually set to red fill. I didn't realize it had to be one that was red via conditional formatting. The problem with that is the cells with the conditional formatting are linked to dynamic data, so they won't always be red after the data updates.

However, to test, I set to M14 on Orders per Company Code which currently has red fill due to conditional formatting (and N from a formula) and ran the macro again. Unfortunately, it's still displaying every single value from row 1 for every single project.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,528
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
range.DisplayFormat.Font.ColorIndex
returns a number that represents a color according an enumeration (see below)

If you use range = a cell with red N it should returns the Font.ColorIndex used in CF

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,528
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Please, tell me what this code returns
B2 --> a cell with red N due to CF

VBA Code:
Sub GetColorIndex()
    MsgBox Range("B2").DisplayFormat.Font.ColorIndex
End Sub

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,245
Messages
5,641,070
Members
417,192
Latest member
choomkey

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