Microsoft Excel complex formula to format specific cells

Question

I think I may have bit off more than I can chew, and need some help / ideas...

I have a spreadsheet with manually numbered Week #s in Column A (cells  A6:A57).

 

I currently use Conditional Formatting with a formula to compare the manually entered Week # in Column A to the current Week # in CA21 (arrived at as the result of cell referencing formulas)

 

Cell CA21 uses the formula: CA 19-1

Cell CA 19 uses the formula: =WEEKNUM(BZ21,17)

Cell BZ21 uses the formula:  =TODAY()

The desired output (which currently works) is to ‘highlight’ the current business week number (used by my company based) for today’s date, by applying a yellow fill and bold font.

What I am attempting to do now, is to create a formula that will find that ‘highlighted’ row (or arrive at the same row using a formula) and then in that same row, find all of the blank/empty cells with no fill and format them as having a red fill. The extra challenge is that I only want this red fill applied when certain criteria are met:

·         Only apply the fill when it is currently Friday.

·         Only want the empty cells in that row to be red fill.

·         Only apply it to the cells in that row starting at Column C and ending at Column BW

·         No red fill applied in other cells in that same row that already contain a color fill or text

·         Want the red fill to disappear (cell by cell) once any value is typed into that cell.

I would like to still be able to override and manually set each cells fill color to red after typing in data (or have it stay red if the data was entered Friday or after – if that is possible)

I have tried playing around with different arrangements of the functions:

=ROW(=$A6:A57=$CA$21),

=IF ((=WEEKDAY(TODAY(),1)) >=6

=HLOOKUP  

But can’t quite get it to work!

 Thanks in advance for any advice or help!!

Answer 1

Select C6:C57 (with C6 the active cell), and use the formula option of CF with the formula

=AND($A6=$CA$21,C6="",WEEKDAY(TODAY(),2)=5)

choosing a red fill.  Then copy C6:C57 and paste formats over the other columns that are not currently filled with other colors.  That formula (and method of applying it) meets all these criteria

·         Only apply the fill when it is currently Friday.

·         Only want the empty cells in that row to be red fill.

·         Only apply it to the cells in that row starting at Column C and ending at Column BW

·         No red fill applied in other cells in that same row that already contain a color fill or text

·         Want the red fill to disappear (cell by cell) once any value is typed into that cell.

For this requirement:

·        I would like to still be able to override and manually set each cells fill color to red after typing in data (or have it stay red if the data was entered Friday or after – if that is possible)

1. Copy this code.
2. Right-Click the sheet tab
3. Select "View Code"
4. Paste the code into the window that appears.
5. Save the file as a macro-enabled .xlsm file.
6. Make changes to the Range("C:E,I:K,O:W,AA:AI")) to include all the columns of interest (I hope you see the pattern.)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Value = "" Then Exit Sub

    If Intersect(Target, Range("C:E,I:K,O:W,AA:AI")) Is Nothing Then Exit Sub

    If Intersect(Target, Range("6:57")) Is Nothing Then Exit Sub

    If Cells(Target.Row, "A").Value < Range("CA21").Value Or _

        (Application.Weekday(Date, 2) = 5 And Cells(Target.Row, "A").Value = Range("CA21").Value) Then

        Target.Interior.ColorIndex = 3

    End If

End Sub

Answer 2

Wow! Thank you! I honestly just thought that this may have not been possible inside Excel...

Proven very wrong! :)

Answer 3

Excel can do almost anything - lots of capabilities: discovering them is the main issue!