Conditional Formatting based on a Match value in another sheet

nishanthc23

New Member
Joined
Nov 22, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have 2 sheets, one with Master Data and other is the Layout template. I want to format the Sheet 2 cells to GREEN color based on the Status column value in Sheet 1, if it is equal to 'Available'.
please anyone advise, can I achieve this by VBA in a range on cells in Sheet 2.

Sheet 1 - Master Data
1637563444597.png


Sheet 2- Layout
1637563749866.png
 

Attachments

  • 1637563412554.png
    1637563412554.png
    4.9 KB · Views: 2

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
466
Code:
Sub test()
Dim rng As Range
For Each rng In Sheets("Layout").Range("A1:E2")
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If rng = .Cells(i, 1) Then
rng.Interior.ColorIndex = .Cells(i, 1).Interior.ColorIndex
End If
Next
End With
Next
End Sub
 

nishanthc23

New Member
Joined
Nov 22, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Code:
Sub test()
Dim rng As Range
For Each rng In Sheets("Layout").Range("A1:E2")
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If rng = .Cells(i, 1) Then
rng.Interior.ColorIndex = .Cells(i, 1).Interior.ColorIndex
End If
Next
End With
Next
End Sub

Where is it checking the condition Status=Available
 

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
466
Ahh, sorry
If rng = .Cells(i, 1) And .Cells(i, 2)="Available" Then

Code:
Sub test()
Dim rng As Range
For Each rng In Sheets("Layout").Range("A1:E2")
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If rng = .Cells(i, 1) And .Cells(i, 2)="Available" Then
rng.Interior.ColorIndex = .Cells(i, 1).Interior.ColorIndex
End If
Next
End With
Next
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,275
Members
425,460
Latest member
Astros1243

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
Top