1

I need to format a cell that is specified by another cell. Basically after many calculations, the cell AL1 contains a cell reference, in this case it is AD48. I want to format cell AD48 and fill it red. This cell reference can, and will change so tomorow it may be AD54. I would then want only cell AD54 to be filled red and not AD48 anymore.

So basically whatever cell is specified in AL1, I want to format that cell (in this case fill it red)

ALOT of googling and no answer. Any help much appreciated!

pnuts
  • 58,317
  • 11
  • 87
  • 139
John Smith
  • 11
  • 2
  • Yes that is corrent. The actual contents of AL1 are the formula =AK2&AH2 where AK2 is AD and AH2 is 48. These are both given by lookup and match formulas and I have combined them to give me the cell I want. The cell is displayed as AD48 but when you click it and look in the formula bar it is =AK2&AH2. Any ideas? – John Smith May 28 '13 at 23:48
  • Still doesn't seem to work. I can send you the file if you like (I can't upload it directly onto here) – John Smith May 29 '13 at 21:04
  • @pnu I am using Microsoft excel 2007. Thanks. P>S I could post a screenshot of the worksheet but I don't know how to :-) – John Smith May 31 '13 at 00:10
  • @pnu See if this link works and you should be able to see the file for yourself. http://temp-share.com/show/FHKd46206 – John Smith May 31 '13 at 15:15
  • @pnu Still won't work for me, I'm just gonna give up. Thanks for your help anyway much appreciated. All I wanted to do was when you open the spreadsheet it just fills the current date in red. Nevermind! – – John Smith Jun 01 '13 at 02:33
  • @pnu Finally managed to get it to work! Thanks very much for your help. FYI here is how the file ended up if you are interested..... http://temp-share.com/show/f3YgitK2n – John Smith Jun 01 '13 at 19:16

1 Answers1

0

Select sheet (click triangle immediately to the left of ColumnA and immediatley above Row1), Home > Styles - Conditional Formatting, New Rule, Format only cells that contain, Cell Value equal to =$AL$1, Format, Fill, Red, OK, OK.

Edit following clarification of requirement:

Change to Use a formula to determine which cells to format and apply

=SUBSTITUTE(CELL("address",A1),"$","")=$AL$1

Edit to add image of part of file uploaded by OP and some CF clarification.

SO16802589 example

Edit OP has an answer that works but posted this as a link in a comment. There is a solution that does not require formulae in AH1:AJ4 now that AA3 contains =TODAY():

=OR(AND($C5=TEXT($AA$3,"mmmm"),C8=DAY($AA$3)),AND($C4=TEXT($AA$3,"mmmm"),C7=DAY($AA$3)))

in Format values where this formula is true: and Applies to =$C$7:$AG$125. (Now formats both day of week and date of month).

Edit. Attempt at clarification (that should have been provided before!)

Selecting C7 as the start point, the formula for conditional formatting checks whether A or B is true:

A] Both the month name (in C5) matches the current month (long form) of =TODAY() in AA3) and the cell immediately below (ie C8) matches the day in AA3,

Or

B] Both the month name (in C4 – that is merged with C5) matches the month and the current cell (C8) matches the day in AA3.

Either case triggers the conditional formatting – hence this is applied in pairs of vertically adjacent cells.

Since the spreadsheet is well laid out (each month 11 rows and Day1 always in ColumnC) this same formula can be applied throughout with the nature of conditional formatting taking care of adjusting the relative references to cells in ColumnC up to the specified limit of Row 125 and in Row7 (or 8) for columns up to the specified limit of AG.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • I only just got my head round the first formula and actually understood it! Thanks though, great replies. P.S managed to get the second formula working (not that I fully understand it yet!) :-) – John Smith Jun 03 '13 at 18:41
  • Can somebody just explain a few things about the second formula? I understand its basically 2 AND functions which are then evaluated by an OR function. So basically it looks to see if either of the 2 AND functions are true. I get that the first AND fucntion checks to see if the month column matches the current month as given by the today() formula in AA3 and the same for the date checking the first date cell i.e C8. What I dont understand is the second AND function, as it is checking $C4 which is a blank cell?? – John Smith Jun 03 '13 at 20:35
  • You are obviously smarter than me. I think we'll leave it at that :-) Thanks for the help (and the other responder) – John Smith Jun 03 '13 at 21:14
  • OK I get the first one. Starting with C5 and C8 matches the month and date (1,2,3 etc) but then why does this highlight the day cell (ie M,T,W,T,F,S,S). I get the the formula will keep the same differece between the cells as it is replicated - e.g C5,C8 - C6-C9- C7-10 and so on so that it will always match the month and date. The second part starting with C4 (blank) and C7 (day) will, in the first evaluation be false because C4 is blank. Again the difference will remain the same, so as the formula is replicated it will become C5,C8 - C6,C9 - C7,C10 and so on – John Smith Jun 03 '13 at 21:43
  • but as you can see in the second instance it will become C5,C8 which is the same as the first half of the formula. So why does it now highlight the date cell (i.e 1,2,3,4,5). Confused.com! – John Smith Jun 03 '13 at 21:44
  • I have found another slight adjustment which I am sure that you were or would be aware of in that I have deleted the cell with the date in (AA3) and instead replaced it in the formula with today() – John Smith Jun 04 '13 at 18:40