CODEWITHSUNDEEP
exceldate
Thomas BP
Thomas BP

Reputation: 1227

Excel 2 sheets looking at date, AND the cell next to it, and give a color to a cell

I need to implant an AND function, so when I look at some dates on "Year sheet" then I need to look at the cell next to it, and if that cell has the text "bit", then it colors the cell on sheet1

=IFERROR(MATCH(DATE(YEAR(Year!$B$2),1,-2)-WEEKDAY(DATE(YEAR(Year!$B$2),1,3))+AG5*7,Year!$B$6:$B$40,0),"")

AG5 = the weeknumber 31 and is an integer.

Can someone guide me, where to add the AND() function.

Year sheet

enter image description here

enter image description here

Upvotes: 0

Views: 61

Answers (1)

Pierre44
Pierre44

Reputation: 1741

As Foxfire And Burns And Burns explained in his comment. One solution would be a conditional formatting. The AND Function works with conditions and returns only TRUE or FALSE, which is what you use in conditional formatting.

Go to Conditional Formating => Create a new Rule => Use a formula to determine which cells to format.

In your example if the formula you posted only returns the row of a Cell and you want to check if the cell next to it has a "bit" inside you can type something in the form:

=AND(A1=yourdate,INDIRECT("C" & MATCH(DATE(YEAR(Year!$B$2),1,-2)-WEEKDAY(DATE(YEAR(Year!$B$2),1,3))+AG5*7,Year!$B$6:$B$40,0),0)="bit")

And replace "A1=yourdate" by your first condition

More Generally on the And and conditional formatting

If I put as a new rule for the whole worksheet: 

=AND(A1="";B1=6)

I obtain the following color every time my two conditions are filled

enter image description here

I hope it helped a bit.

Upvotes: 1

Related Questions