Using a drop list in the row, and a legend to format same row.

[coake]

Hey guys,
I've searched the forum but I must not be describing what I would like to accomplish properly. There was 1 thread very similar, but it got derailed super fast.

I would like change the cell format of the entire row (A3:F3) based on the drop down (column C) to match the formatting contained in the legend. Column C is a drop down list of the legend. If the legend changes, I'd like the row to change to match. The formatting includes font color, background color, strike thru etc.

I'm assuming VBA is necessary, since CF won't change if the legend formatting changes.


[TABLE="class: grid, width: 456, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]#[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Name[/TD]
[TD]Cost[/TD]
[TD]Schedule[/TD]
[TD][/TD]
[TD]Legend[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]23-Jun-17[/TD]
[TD]Rejected[/TD]
[TD]Task 1[/TD]
[TD]
[/TD]
[TD]1 day[/TD]
[TD][/TD]
[TD]Drafted[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]23-Jun-17[/TD]
[TD]Closed[/TD]
[TD]Task 2[/TD]
[TD]$5.00[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]Submitted[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23-Jun-17[/TD]
[TD]Drafted[/TD]
[TD]Task 3[/TD]
[TD]
[/TD]
[TD]-2 days[/TD]
[TD][/TD]
[TD]Closed[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]23-Jun-17[/TD]
[TD]Submitted[/TD]
[TD]Task 4[/TD]
[TD][/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]Rejected[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]23-Jun-17[/TD]
[TD]Closed[/TD]
[TD]Task 5[/TD]
[TD]
[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]<strike>Cancelled</strike>[/TD]
[/TR]
[TR]
[TD]<strike>6</strike>[/TD]
[TD]<strike>23-Jun-17</strike>[/TD]
[TD]<strike>Cancelled</strike>[/TD]
[TD]<strike>Task 6</strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Internal[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I'm assuming VBA is necessary, since CF won't change if the legend formatting changes.

VBA code will not react to the changing of the formatting of a cell either (there are no event procedures available for monitoring that kind of change), so you might as well implement this in CF. With CF or VBA event code, you will have to refresh the sheet (workbook?) whenever you change the color or font characteristics of the cells in your legend range.

 

[Kenneth Hobson]

A Sheet's Change event can easily make the changes based on say Column C changes in value or column H changes. For the H column's value changes, I guess you are using a Data Validation as in Column C for Column F. If you can post code for the changes for each of the values in the Data Validation List (usually a named range of values), we can code that.

One easy way to handle format issues and reduce code would be for the named range to have the formatting for each cell, including cell borders as in the legend column.

I do see a disconnect though. I would think that Column C changes to some value would be for the same formatting as Values for Column H changes. I can see a case where you have a Value of Draft in column C and Final value in Column F. It does not make sense to me that you would want Final formatting for A:F based on when Column C has Draft. Of course it does not matter to me.

As Rick said, changes based on Font color or such will not trigger the Change event.

 

[coake]

Hey Rick,
Thanks for the input, you are of course correct.
Being a live document that changes very frequently, I was hoping that SelectionChange would work.
If someone were to change the legend, they would click somewhere else anyway.

 

[coake]

Maybe I should be asking if I can change the format of cells(A3:F3) to match whichever the format is in the Legend range that = C3 and then C4 etc.
C3 is the only data validation list where the contents of the drop-down list are in the Legend (the rest of the row are text cells).

I apologize, I know I'm not explaining very well what I'm trying to achieve.

Basically, someone can change what the formatting of the row should look like if "Drafted" is selected, or any of the 15 different status' are selected. This can change from Project to Project.
I could 'hard-code' the formatting in the conditional formatting and say if C3 = Drafting, format it like this. If C3 = Submitted format it this other way and so on.
The concern is, for 1 project "Drafted" may be Green back ground with black font. On a different project "Drafted" could be yellow back ground with red font color and subscript etc.
If I use the conditional formatting, someone else using the spreadsheet will have to go into the conditional formatting and change the way the formatting is for each of the X number of different status' instead of just formatting the legend as required.

I hope that clears up what I'm trying to accomplish .

 

[Rick Rothstein]

Maybe I should be asking if I can change the format of cells(A3:F3) to match whichever the format is in the Legend range that = C3 and then C4 etc.
C3 is the only data validation list where the contents of the drop-down list are in the Legend (the rest of the row are text cells).

I apologize, I know I'm not explaining very well what I'm trying to achieve.

Basically, someone can change what the formatting of the row should look like if "Drafted" is selected, or any of the 15 different status' are selected. This can change from Project to Project.
I could 'hard-code' the formatting in the conditional formatting and say if C3 = Drafting, format it like this. If C3 = Submitted format it this other way and so on.
The concern is, for 1 project "Drafted" may be Green back ground with black font. On a different project "Drafted" could be yellow back ground with red font color and subscript etc.
If I use the conditional formatting, someone else using the spreadsheet will have to go into the conditional formatting and change the way the formatting is for each of the X number of different status' instead of just formatting the legend as required.

I hope that clears up what I'm trying to accomplish .

Oh, so the Legend colors, once set, are not going to be change for any worksheet they appear on. In that case, you can use this VBA event code to color the data cells to match the item in Column C's list which matches the Legend. Using the data layout you posted in Message #1 , here is that event code...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LegendCell As Range
  If Target.Column = 3 And Target.Row <= Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row Then
    Set LegendCell = Columns("H").Find(Target.Value, , , xlWhole, , , , False)
    Application.EnableEvents = False
    With Intersect(Columns("A:F"), Target.EntireRow)
      .Font.Color = LegendCell.Font.Color
      .Interior.Color = LegendCell.Interior.Color
      .Font.Strikethrough = LegendCell.Font.Strikethrough
    End With
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Marcelo Branco]

Another - similar to Rick's code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFound As Range
    
    If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        Set rFound = Range("H2:H7").Find(Target.Value, _
                LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        
        If Not rFound Is Nothing Then
            With Range("A" & Target.Row).Resize(, 6)
                With .Font
                    .Color = rFound.Font.Color
                    .Bold = rFound.Font.Bold
                    .Strikethrough = rFound.Font.Strikethrough
                End With
                .Interior.Color = rFound.Interior.Color
            End With
        End If
    End If
End Sub

M.

 

[coake]

Thanks Rick, that works really well!