Conditional formatting a cell depending on another cell

[BakirDZ]

Hello,

I would appreciate if anyone could help me with what I'm trying to do (not sure if it's possible though)

I have 2 lists of products required by 2 customers and some products are required by both customers, I go through the customer_1 list item by item and check if it's available in stock, if it's not I color its quantity with red. I wonder if there is a way to make excel do the same with customer_2 by coloring the cells of quantity for the products that are required by customer_2 and already been marked as unavailable for customer_1

Thank you guys in advance!

 

[Joe4]

Welcome to the Board!

Conditional Formatting can only run off of cell's values, not the manual formatting applied to them.
If you are manually coloring the cells for Customer_1, you cannot Conditional Format cells for Customer_2 based on that. You would require VBA to color their cells.
If the cells for Customer_1 were being formatted via some Conditional Formatting rule, you should be able to apply the same Conditional Formatting rule to Customer_2.

 

[BakirDZ]

Welcome to the Board!

Conditional Formatting can only run off of cell's values, not the manual formatting applied to them.
If you are manually coloring the cells for Customer_1, you cannot Conditional Format cells for Customer_2 based on that. You would require VBA to color their cells.
If the cells for Customer_1 were being formatted via some Conditional Formatting rule, you should be able to apply the same Conditional Formatting rule to Customer_2.

Hey Joe4, thanks for your reply and welcome

Do you mean that I need VBA to do this or it's not possible at all if I color the cells manually ? and if it's possible with VBA I would appreciate your help with it if possible, Thanks

 

[Joe4]

You need VBA if you are coloring the cells manually.
Here is VBA code that will do what you want, based on your example. I added comments to the code to explain what each step is doing:

Sub MyCopyFormatting()

    Dim lr1 As Long
    Dim lr2 As Long
    Dim i As Long
    Dim j As Long
    Dim prd As String
  
    Application.ScreenUpdating = False
  
'   Find last row in column B with data
    lr1 = Cells(Rows.Count, "B").End(xlUp).Row
  
'   Find last row in column E with data
    lr2 = Cells(Rows.Count, "E").End(xlUp).Row
  
'   Loop through all rows in column B starting with row 4
    For i = 4 To lr1
'       See if column C is highlighted
        If Cells(i, "C").Interior.Pattern = xlNone Then
'           Do nothing if no highlighting
        Else
'           Capture value from column B
            prd = Cells(i, "B").Value
'           Loop through column E looking for matches
            For j = 4 To lr2
                If Cells(j, "E") = prd Then
'                   Copy formatting from column C to column F
                    Cells(i, "C").Copy
                    Cells(j, "F").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                    Application.CutCopyMode = False
                End If
            Next j
        End If
    Next i
  
    Application.ScreenUpdating = True
  
End Sub

And here is my proof-of-concept.

Before Macro is run:

After Macro is run:

 

[BakirDZ]

You need VBA if you are coloring the cells manually.
Here is VBA code that will do what you want, based on your example. I added comments to the code to explain what each step is doing:

Sub MyCopyFormatting()

    Dim lr1 As Long
    Dim lr2 As Long
    Dim i As Long
    Dim j As Long
    Dim prd As String
 
    Application.ScreenUpdating = False
 
'   Find last row in column B with data
    lr1 = Cells(Rows.Count, "B").End(xlUp).Row
 
'   Find last row in column E with data
    lr2 = Cells(Rows.Count, "E").End(xlUp).Row
 
'   Loop through all rows in column B starting with row 4
    For i = 4 To lr1
'       See if column C is highlighted
        If Cells(i, "C").Interior.Pattern = xlNone Then
'           Do nothing if no highlighting
        Else
'           Capture value from column B
            prd = Cells(i, "B").Value
'           Loop through column E looking for matches
            For j = 4 To lr2
                If Cells(j, "E") = prd Then
'                   Copy formatting from column C to column F
                    Cells(i, "C").Copy
                    Cells(j, "F").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                    Application.CutCopyMode = False
                End If
            Next j
        End If
    Next i
 
    Application.ScreenUpdating = True
 
End Sub

And here is my proof-of-concept.

Before Macro is run:
View attachment 98476
After Macro is run:
View attachment 98477

That's exactly what I was trying to do, thank you so much!

 

[Joe4]

You are welcome.
I am glad I was able to help and that it worked out well for you!