How can someone use conditional formatting based on the cell in the sheet itself dynamically?

Question

I am currently trying to use conditional formatting for my classes and I want to highlight a row of cells if the value in a specific column is equal to the value in a specific cell. If you see in the picture, the following is my thought process:

1. The applicable range is from A:G (121 because that ends the sheet)
2. Custom formula is =$G3=I16 which I believe to search the entire column G (Course Names) and if it is equal to the value of I16 (Specific course name from a key) then highlight it red. However, when running it, only 2 random cells get highlighted as shown in the screenshot.

Can anyone tell me if this is possible and if so, where my thought process was wrong? Thank you in advance!

I already tried manipulating some of the values:

=$G$3=I16
=$G3=I17
=$G3=INDIRECT(I16)

It does work if I use the value of the cell itself (ie., Women of Christian East, Consumer Behaviors, etc.) instead of the cell. Please note I am also not too familiar with the indirect function!

I also tried to play with the actual cell values to see if it was not referencing the text so I created an IF statement in the sheet which returned true. I wasn't sure what else to try beyond formula manipulation but I am happy to answer any other questions and add more as needed. Table showing all of the raw data. Data is sorted in order of soonest date to latest date. Column I shows the courses as a key value pair and is colored coded.

Answer 1

Not random cells are highlighted, but according to your rules. First, your range is A1:G121 and formula starts at G3, so it looks for condition at G3 ant applies it to A1, then looks at G4 and applies it to A2 and so on. Second, value you compare to must have absolute reference, so it doesn't "move" for each cell in range you are applying to. Try formula:

=$G3=$I$16

Applied to range:

A3:G121

Result:

Also take a read: absolute vs relative reference