Conditional Formatting and output to a range

dave3944

Board Regular
Joined
Jan 22, 2005
Messages
139
Another application, another opportunity to learn.

The setup:
I have a Worksheet (DWG Rev) in a Workbook that compares two values (Drawing Revision Letters) and displays in red (via Conditional Formatting) when the values are not equal. This alerts the user that a change has been made. If they see no red, they don't have to manually review each Drawing for changes. This saves a lot of time. But then they have to manually look up each Drawing # in red to see what changes have been made. This is also very time-consuming.

What I need is a macro that looks through the list (which varies in length, but is always contiguous) and picks out the values which have been made red by Conditional Formatting, place them in a list and display them in a VBA UserForm. The user would then select (via OptionButton) if they wanted to view the Drawing. If yes, Adobe Acrobat would open (the Drawings are in PDF format) and display the Drawing.

So far, I can open Adobe Acrobat to the correct path with no problems. I also went to the Chip Pearson website and used some of the Conditional Formatting macros, but can't get them to return anything but an empty string for the range of red cells.

I need to know how to populate the list of red cells so I can make them available to the UserForm.

Any help is greatly appreciated. You always come through for me.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Not sure if this will help, but, it might help you if you use the actual conditon, instead of the color, to return the desired cells.
 
Upvote 0
Great tip!!! That's why I come here. I've been using it until I got stumped. I have found a way to identify all the items that are different, but I need to add all of those items to a variable that can be passed to a UserForm. I haven't been able to figure that one out. What I have so far is:
Code:
Public Sub CompareCells()

Dim LastRow As Long
Dim RevNew As String, RevOld As String
Dim x As Integer
Dim DwgList As Collection
Set DwgList = New Collection
Dim dwg As Variant

' Identify & Assemble Drawing #'s to populate ComboBox
'
' Count the rows of "DWG Rev"
    LastRow = Worksheets("DWG Rev").Range("C65536").End(xlUp).Row
    
    For x = 11 To LastRow
    
' Define the cells to compare
    RevNew = Sheets("DWG Rev").Range("C" & x).Value
    RevOld = Sheets("DWG Rev").Range("G" & x).Value
    
    If RevNew <> RevOld Then

' If true then add contents to DwgList

        AddToStack DwgList, dwg

    End If
    
' Go back to the loop

    Next x

End Sub

Sub AddToStack(WhatStack As Collection, WhatItem As Variant)
WhatStack.Add WhatItem
End Sub
Another question - How do you list all the unique items in the DwgList collection? In other words, How do you know that all the members you wanted to be selected from the original list were added to DwgList?
 
Upvote 0
Sorry, I'm not into VBA, so, hopefully, someonce else will be addressing your problem. Good luck!
 
Upvote 0
I guess what I need to know is: How do I take the code I have, populate the variable DwgList with the list of changed drawings and pass that list to a UserForm. If there are two changed drawings, I want to feed the variables for the Drawing #'s to the UserForm. Along the lines of: "These are the drawings that have changed, which one do you want to see?"
Is it possible to populate a UserForm Option Button with a variable?
What I have: the list of changed Drawing #'s; a way to open the PDF files associated with the Drawing #'s. I need a strategy to pass those Drawing #'s to a UserForm for the User to choose for viewing.
Thanks in advance for the help.
 
Upvote 0
Sorry for any confusion I may have created.

The original concept has been changed due to the suggestion of a Board Member. I no longer need to key off of Conditional Formatting, instead, as you can see, the code focuses on the condition of the cells.

I need to know: How do I populate the variable DwgList with the list of changed drawings and pass that list to a UserForm. Is it possible to populate a variable number of UserForm Option Buttons with a variable? For example, if three Drawings have changed, create three Option Buttons, each with the Drawing #. Along the lines of: "These are the drawings that have changed, which one do you want to see?"

What I have: the list of changed Drawing #'s; a way to open the PDF files associated with the Drawing #'s. I need a strategy to pass those Drawing #'s to a UserForm for the User to choose for viewing.

As a side, how do I check to see what the variable DwgList is populated with?

Thanks for your time.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top