Conditional Formatting and output to a range

[dave3944]

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.

 

[RalphA]

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.

 

[dave3944]

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:

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?

 

[RalphA]

Sorry, I'm not into VBA, so, hopefully, someonce else will be addressing your problem. Good luck!

 

[dave3944]

Can anyone help with this?

 

[dave3944]

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.

 

[dave3944]

Anyone?

 

[dave3944]

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

 

[dave3944]

Any help on any part of the problem would help.

Thanks.