VBA to find cell with a certain value and format it

[jaywrye]

I have 2 pages, one in the form of a list (Sheet1) and another one in the form of a calendar format (Sheet2). I want to create a code that can help me to locate the name of the assignment taken from Sheet 1 on Sheet 2. If the assignment exists on Sheet2, check for the status of the assignment. If it is "Completed", I want the name of the Assignment on Sheet 2 to be colored in green and if it is "Pending", then the name of the assignment will be in red. Does anyone have any idea on how I can get about doing this? I will illustrate the steps below with pictures.

	This is sheet 1. Here, I will have a list of assignments that will be populated in Sheet 2 based on the dates I have specified.
	This is sheet 2. What I want to do here is to loop through every cell within the range of the calender (B7:H17) and match it against the list of assignments I have created in Sheet1. If the Assignment exists, then check for the status of the assignment and update the color for the assignment correspondingly.

 

[Fluff]

Hi & welcome to MrExcel.
Does sheet 2 have formulae to return the assignments & when there are 2 or more are the concatenated together in the same cell?

 

[jaywrye]

Yes there is. I use this formulae:

=IFERROR(TEXTJOIN(", ", TRUE, IF(Assignments[DUE DATE] = 'Month View'!B46, Assignments[DESCRIPTION], "")), "")

 

[Fluff]

In that case you will not be able to do it. You could use conditional formatting to change the font colour, but that works on the entire cell.
Even with VBA you would only be able to change the colour for the entire cell as it has a formula.

 

[jaywrye]

Oh, if I were to transfer the formula for the cells into VBA and perform the formatting in VBA instead, will that be workable?

 

[Fluff]

To have two assignments different colour in the same cell you would need to get rid of the formula.

 

[jaywrye]

Hmm I see ok I will try to think of another way to do this. Thanks for your help Fluff

 

[Fluff]

You're welcome & thanks for the feedback.