Conditional formatting with VBA

[Custersroel]

Hi,

I need to apply conditional formatting to a certain range of cells, based on BOTH the content of the cell itself and the text of the cell in column A of the specific row.

See attached simpified example.
For every cell in the range D1:M3 the background color should only change if there is an "x" present in that cell.
The color should depend on the choice that is made in column A of the specific row where the cell is in.

Thus, 4 different colors based on selection in column A, but only apply to cells that have "x" in them.

Can anyone supply me the code for this.....
I've tried somethings myself, but have no experience with VBA.

[JBeaucaire]

What colors?

You're using a drop down box in column A with a validation list =Status but that list isn't in this sample sheet, so we don't see what the values are...the drop down box doesn't work for us.

Are the "X" values in the chart being put in manually? If so, we can use a Worksheet_Change macro to watch your manual changes and make color changes at that moment.

If the "X" values are being put in by formulas, then we would need another approach.

Can you update your sheet so that it shows all the values possible in column A and the color choices for each selection?

What would be BEST is another copy of that chart a little further down colored up manually the way you would like it to look. That would make it easiest for us to see the goal line.

[Custersroel]

Ok, added the list with the 4 colors.

The columns D to M contain data that HAS BEEN input manually by me, but is now protected.
Actual users of the sheet will ONLY change the status in column A.
Upon this changing of status the color of the cells containing "x" in that row should change.

Hope it's more clear now!

[JBeaucaire]

I've added the following worksheet_change event macro to your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim trow As Long
If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A:A")) Is Nothing Then
trow = Target.Row
    
Select Case Target.Value
    Case "Updated and checked"
        With Range(Cells(trow, 4), Cells(trow, 13))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""x"""
        .FormatConditions(1).Interior.ColorIndex = 50
        End With
    Case "Checked"
        With Range(Cells(trow, 4), Cells(trow, 13))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""x"""
        .FormatConditions(1).Interior.ColorIndex = 43
        End With
    Case "Change request"
        With Range(Cells(trow, 4), Cells(trow, 13))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""x"""
        .FormatConditions(1).Interior.ColorIndex = 45
        End With
    Case "Not checked"
        With Range(Cells(trow, 4), Cells(trow, 13))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""x"""
        .FormatConditions(1).Interior.ColorIndex = 3
        End With
    Case Else
        With Range(Cells(trow, 4), Cells(trow, 13))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""x"""
        .FormatConditions(1).Interior.ColorIndex = xlNone
        End With
    End Select
End If
End Sub

Basically, as you make changes in the A column, it applies a conditional formatting to the same row in D - M. The conditional formatting makes the color change if the cell has an "x" in it.

[Custersroel]

Seems like you attached the wrong file?

Anyway, I tried copying and adding the code to my example sheet, but nothing seems to happen when I select something in column A?

[JBeaucaire]

My bad. Here you go:

[JBeaucaire]

This is a worksheet macro, it goes in the Worksheet module, not a standard module. Locate it on my sample to see what I mean.

[Custersroel]

Works like a charm!
Thanks for the quick help, my boss will be happy, haha!

[JBeaucaire]

Awesome. Hey, if you're going to take credit (which I hope you DO!), I get at least a postcard from Holland for my "international friends" shelf, right? Hehe....

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[Custersroel]

Awesome. Hey, if you're going to take credit (which I hope you DO!), I get at least a postcard from Holland for my "international friends" shelf, right? Hehe....

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Since you found the solution for me so fast, I don't think it was quite that hard for you and therefore you don't deserve a postcard just yet.
Therefore I came up with another challenge. If you manage to solve this for me I will send you a postcard from Taiwan (my current location) and even one when I get back in Holland, deal?

In other words, please help.....

See example sheet again.
Data in columns A and B is copied from external sheets by a macro which is triggered manually. Based on the data in A and B, column C will get a certain status. As you can see it is not the most pretty formula, but hey, it takes care of my needs and I'm proud of it
Anyway, based on the status in Column C I want the conditional format as discussed before to be applied to columns F through O.
In this case the Worksheet_Change event does not work anymore, since the target cells (in column C) are changed by a formula and not by manual input.
How to solve this?

[JBeaucaire]

Here's the macro and a sample sheet. Added a column AA to assist in the random calculation verification of changes. You can hide that row if it it's bothersome.

Private Sub Worksheet_Calculate()
Dim rng As Range, cell As Range, lastrow As Long
lastrow = Range("F" & Rows.Count).End(xlUp).Row

Set rng = Range("C1:C" & lastrow)

For Each cell In rng
    If cell.Value <> cell.Offset(0, 24).Value Then
        cell.Offset(0, 24).Value = cell.Value
        Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions.Delete
        Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""x"""
    
    Select Case cell.Value
        Case "Updated and checked"
            Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions(1).Interior.ColorIndex = 50
        Case "Checked"
            Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions(1).Interior.ColorIndex = 43
        Case "Change request"
            Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions(1).Interior.ColorIndex = 45
        Case "Not checked"
            Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions(1).Interior.ColorIndex = 3
        Case Else
            Range(Cells(cell.Row, 6), Cells(cell.Row, 15)).FormatConditions(1).Interior.ColorIndex = xlNone
        End Select
    End If
    Next cell
    
End Sub

EDIT: staring at that code, I realized I could shorten it a great deal.

[JBeaucaire]

NOTE: The two lines in red above could be deleted, I'm not 100% on that but I believe logically it would be fine if you were expanding the chart by copying down previous rows.

I'm 100% confident you can delete the first line, but you might want to experiment on your sheet and how you expand it to make sure the second line is OK to delete.

[Custersroel]

NOTE: The two lines in red above could be deleted, I'm not 100% on that but I believe logically it would be fine if you were expanding the chart by copying down previous rows.

I'm 100% confident you can delete the first line, but you might want to experiment on your sheet and how you expand it to make sure the second line is OK to delete.

Once again, it works like a charm!
I haven't tried deleting those lines, but if I would delete the 2nd line then the format would not only be applied to cells with "x" in them I guess.
Anyway, PM me your address!