To highlight cells in columns:
A (Index), B (Name), C (Main Exam Paper Mark) and F (Total Marks)
Note: your column letters and names in 'Problem 1' do not match the columns given when you described your setup. I have used the column letters and names used in the setup description.
To highlight these in red, do this:
Select cells A2, B2, C2 and F2 (use the ctrl key to make the non-adjacent cell selection)
From the Ribbon select Home - Styles - Conditional Formatting,
From the drop down select 'Manage Rules' and select 'New Rule'
In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
Enter this formula in the box
Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
The cell reference to C2 must use a $ sign like this: $C2
Click the format button and from the 'Fill' Tab select a red color, click OK
Click OK and close the conditional formatting dialog box.
The cells in columns A, B, C & F will now be red if the cell in column C is empty (but not if it is zero)
As this conditional formatting will be applied to cells before an Index value is entered and therefore the row is empty, I suggest revising the formula so that the red color is not applied if column B is empty.
Use this formula instead:
Again note the $ signs in the cell addresses.
Select cells A2 to F2, Copy and then select all the cells in columns A to F from row 2 down to the last row you intend to use and Paste Special... - Paste Formats.
Decide how many rows you will be using. For this example I used rows 2 to 50 on the Main Mark Sheet
Select cells B2 to C50 (adjust as appropriate)
With these cells selected in the address box just above the row numbers and to the left of the column letters replace the cell address showing with "MainMarks" (don't enter the double quotes), then click Enter with the cursor still in the address box.
This allows the range B2 to C50 to be referenced by the name "MainMarks" instead of by a worksheet name and cell reference.
[Apply this to both worksheets Extra-Curricular Mark & Drawing Skills Mark]
Select cells A2 to D2.
Follow the conditional formatting instructions and enter this formula:
Add a Blue format & OK out of conditional formatting.
Copy cells A2 to D2 and as before Paste Special ... Formats to the rows you plan to use - I selected B2 to D50, to match the number of rows setup on the Main Mark Sheet
Now, when a name appears in column B on the Extra-Curricular Mark worksheet, but the cell in column C on the Main Mark Sheet on the same row as their name is empty, the cells will be colored blue.
You haven't said what formula is in the cells in column D.
I have used a VLOOKUP to find the name from column B in column B on the "Extra-Curricular Mark" worksheet and return the value from column D of that person's row.
I have included a message "Name not found" when the name in column B is not found on the other worksheet.
I have combined a test for no name in column B with a test for no entry in the Main Exam Paper Mark column (C), using OR ... OR(B2="",C2="")
If either is true, no lookup is performed to try and find the mark from the "Extra-Curricular Mark" worksheet. This was my final formula in cell D2:
"Name not found", VLOOKUP(B2,'Extra-Curricular Mark'!$B$2:$D$6,3,FALSE)))
(Formula split onto more than one line for ease of viewing)
Copy D2 and Paste Special... Formulas to cells D3 to D50
Repeat for column D "Drawing Skills Mark"
Select cells D2 to D50 on the "Extra-Curricular Mark" worksheet
Select Data Validation (I don't have Excel 2007 on this PC, so I can't give you the exact sequence)
In the data validation dialog box select the settings tab, and under 'Allow:' select 'Whole number'
Enter the values for 'Minimum:' and 'Maximum:' and click OK
Now you will not be able to enter values outside the range in these cells.
[You could use conditional formatting to set the font color to Pink when a value outside the range 0 to 50 is entered, but I think that this data validation approach is more appropriate in this case. The conditional format formula would be
=IF(OR($D2<0, $D2>50),TRUE,FALSE) and Format... - Font - Color:].
Repeat for D2 to D50 on the Drawing Skills Mark" worksheet.