Conditional Formatting only top line of cell

[jomili]

I have a worksheet with conditional formatting applied. My formula works to change the cell color, font color, and strike-through the font if a "yes" answer is indicated. It all works well EXCEPT that the font color and strike-through ONLY apply to the top line on my cell.

Each cell has a title line, and a few paragraphs in it, with separations between the title line and the paragraphs. I kind of like having the title line a different font, but when it's time to strikethrough I want ALL of the text struck through.

Any ideas on how to do this? Here's an example of my conditional formatting,

Formula is =INDIRECT("D"&ROW())="Yes"
Format Font Blue, Strikethrough, Background Green,

and a sample of the text in a cell:

Summary of letter from Boss dated 12/2/09 titled "FW: Incentive funding and FTE's"

So and So requested information on FY09 balances on Incentive funding, which has been expended.  None was available in FY10 for salaries due to the original FY10 Budget in Dallas having "Other Operating"(over 380,000) but no Salary Budget.  Dale is trying to  cover the three Active Positions that are in Refugio for FY10, which were supposed to be in Eagle Pass only temporarily for FY09.

Per conversation with Penelope, due to the temporary nature of these FTEs she would like to fill one of them (the other two are vacant and the third she anticipates will be vacated in the near future) with a temporary employee through the remainder of the fiscal year.  I informed her this is fine, but the FTEs may not be filled past August 31, 2010. Also, I recommended whomever is hired be hired as a company employee (in lieu of through a temp agency) to fund the cost through salaries. 

This will need to be noted as an FY11 Action Item, that the 3 Matching Specialist FTEs (associated with El Paso) are inactivated.

[NBVC]

You can't partially conditionally format a cell....


Maybe with some VBA... but not with native Excel.

[jomili]

Please re-read my post. Perhaps I phrased it awkwardly, but the problem is that Excel IS partially applying conditional formatting, and I want it to apply the conditional formatting FULLY. Excel now is applying the font attributes of the conditional formatting ONLY to the top line; I want it applied to all of the text in the cell.

Also, one thing I think may be the culprit, is that the Title in the cell (top line) I manually format as italic, whereas the rest of the font is non-italic. If I make the italics-setting consistent in the cell, the conditional formatting works properly. But I'd like to keep the top line italicized to make it stand out. Any good ideas?

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[jomili]

I've attached my samples. There are two tabs. In the first, the conditional formatting is working correctly, because all font is non-italicised. In the second, the top line in each cell is italicized, and so only that line is conditionally formatted correctly. I'd like to have the italicized first line in the cell, but apply the conditional formatting to the entire cell.

[NBVC]

Hmmm.. interesting.. the fact that you manually partly formatted the cell to italics... "confuses" the conditional formatting.. I guess because it looks at maybe the first character of the cell to check the format and to see whether it needs to change it or not... and not the entire cell content.... I tested it by just changing the first character back to normal font, then the strike-through only applies to that first character...

Not sure how to make it ignore the already applied format...

[jomili]

"Interesting" is a nice, non-emotional way to look at it.

To make the conditional formatting behave I have to change the formatting of the entire cell, and make it consistent, so the conditional formatting kicks in. I can live with not having the top line italicized, but it's certainly not what I want. If you can find me a workaround I'd sure appreciate it. I haven't looked into any VBA code for this one yet, as I was hoping there was a native way with Excel to do this.

Thanks for all your involvement so far.

[shg]

Maybe like this. Code goes in the Sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column <> 3 Then Exit Sub
        
        With Me.Cells(.Row, "A").Resize(, 3)
            Select Case LCase(Target.Text)
                Case "yes"
                    .Interior.ColorIndex = 35
                    .Font.ColorIndex = xlColorIndexAutomatic
                    .Font.Strikethrough = True
                
                Case "no"
                    .Interior.ColorIndex = 40
                    .Font.ColorIndex = 3
                    .Font.Strikethrough = False
                
                Case "unknown"
                    .Interior.ColorIndex = 36
                    .Font.ColorIndex = xlColorIndexAutomatic
                    .Font.Strikethrough = False
                    
                Case Else
                    .Interior.ColorIndex = xlColorIndexNone
                    .Font.ColorIndex = xlColorIndexAutomatic
                    .Font.Strikethrough = False
            End Select
        End With
    End With
End Sub

[NBVC]

Sorry about downplaying it .. I know it is frustrating... but I also discovered that in XL2007, it formats as you desire (i.e. the whole thing strikes through and leaves first line italicized)... so probably a 2003 bug or something that got noticed and fixed later....