Highlight a cell if another cell is highlighted

[jlbarkley]

I have built a pretty extensive budget spreadsheet with a lot of different formulas. One formula I have is when a given budget parameter such as (mortgage) is paid then that row highlights yellow so I know there is no more money available for that budget group. This is the same for groceries or spend.

On a third sheet I have a list of all the bills with due dates. I am trying to make it where when that line item goes yellow, or my remaining amount left goes to 0, then this cell that corresponds with that budget will also turn yellow so I know that bill has been paid.

For instance I have mortgage, groceries, spend, etc in my budget sheet. I also have a check register in another sheet. When the budget assignment in the check register matches the budget sheet then it will automatically subtract money for that line item. So if in my budget I have 400 for groceries and in my check register I spend 100 on groceries then on my budget sheet it will say I have spent 100 and have 300 left. Once that 300 left goes down to zero then that whole line turns yellow. I am wanting to match that same thing on another sheet for when that word goes yellow I want the word on the due date page to turn yellow as well.

Hopefully the pictures make it more clear. I understand the words will need to match. Just trying to get the formula correct.

 

[GlennUK]

Is the test just that if the amount for the word in the other sheet is zero, then highlight? If so, a simple Conditional Format, using a formula, with a formula to point to the corresponding amount and test for zero, and a chosen fill colour would do it.

 

[jlbarkley]

I could definitely do a simple conditional formatting to test for 0. I’m just not sure how to tell that cell to test for zero IF the cell beside it matches that word. In pic 1 if C1 (probably actually not the cell) goes to zero then that row highlights. My budget changes quite a bit so I don’t want to have to put the budget in the exact same order every time. I want to be able for the due date tab to test for the specific word in cells A1-A10 (pic 1) and highlight yellow when the cell 2 cells to the right (C1-C10) gets to zero. Hope this makes sense.

 

[GlennUK]

How come you seem to be completely ignoring the fact that I said " with a formula to point to the corresponding amount"?

 

[jlbarkley]

Obviously I’m coming on the board because I need help…… maybe I didn’t understand what that meant and thought I did…. But glad you helped….

 

[GlennUK]

Obviously I’m coming on the board because I need help…… maybe I didn’t understand what that meant and thought I did…. But glad you helped….

Select the cell you want to turn yellow when the corresponding value in another sheet is zero, and do Conditional Formatting/Formula with a formula like =INDEX(other_sheet!value_col,MATCH(word_cell_here,other_sheet!words_column_in_other_sheet,0))=0 and choose yellow fill. Alter that formula to point to the places I've named the range items as, as clues for you.

 

[jlbarkley]

Got it! This worked. Thanks again!