VBA: Conditional Formatting rows when any cell in row has something in it

[Frankie_The_Flyer]

Hi

In the attached worksheet the data is dragged and dropped from (say) row 80 up to a row in the Input columns above it. (NOTE The data has already been distributed in the attached sheet). As the range for the data covers columns F:AS (so some columns will be off screen) I want the full row the data is dropped in to change colour to warn me that data is in (say) Input 1 row 8 so I don't put anything else in row 8 in other Input columns.

I have tried various ISBLSNK, ISNOTBLANK, COUNTA($F5:$AS5)>0 etc, but I can only get either the whole selection (F5:AS79) to be highlighted or none of it.

(I don't know if dragging and dropping has an effect on the Conditional formatting)

Any ideas please?

Thanks

Frankie

[protonLeah]

Try:

Please Login or Register  to view this content.

[Frankie_The_Flyer]

Thanks for the response protonLeah.

I added in the other input columns to the code you've suggested and made the "Apples to" cell in the condition $F5:$AS200. However, the whole table down to row 84 colours in although there is no data entered from row 70 downwards.
When I go back to the condition the "Applies To" field has =$F$5:$AS$66,$F$69:$AS$200,$F$67:$AA$68,$AD$67:$AS$68 in it.

Also, if I move (drag & Drop) data from one row to another, it just leaves the cells the data has come from with no format but the rest of the row remains formatted in the selected colour.

There are no other conditions in the sheet so I'm a bit lost as to what's going on.

Any ideas please??

Revised sheet with the complete code and the extended format code attached.

Frankie

[AliGW]

(I don't know if dragging and dropping has an effect on the Conditional formatting)

Yes, it does. A simple drag and drop operation copies and pastes everything, including formatting options. However, that shouldn't matter if the rule is set up correctly.

I don't understand the logic behind the formula in the Word document: why are you dividing the row into segments that are adjacent to each other? Why not just define the whole row? From your description, you want the whole row to highlight if anything appears in it anywhere.

Not sure I'm confident that I understand what you want to achieve.

[kvsrinivasamurthy]

The problem is because of formulas.

Remove the existing CF.
Select $F$5:$AS$200
Formula for CF is

=COUNTIF($F5:$AS5,"?*")>0

[Frankie_The_Flyer]

Hi AliGW

The formula I provided is an extension of the one from protonLeah. (Thought I'd give it a go!!).

The response from kvsrinivasamurthy is doing what I'm after; highlighting a whole row between F & AS if any of the cells in that row has data in them.

I now understand that the issue I have is about the drag dropping. It all works fine until I need to drag about during the sorting.
EXAMPLE: If I move the data in Input 2 Row 10 down to Input 2 Row 15, Row 10 drops the formatting (all good!), but Input 2 Row 15 loses formatting (the rest of the row remains formatted). If I then move the data in Input 2 Row 15 back to Row 10 the Input 2 Row 15 cells stay un-formatted.

I wonder if I need to go to VBA and format when the cell info changes?

Ho Hum! Back to the drawing board!!

Thanks

Frankie

[Frankie_The_Flyer]

Thanks for the response kvsrinivasamurthy .Excellent work!!

As per my response to AliGW, I now understand that the issue I have is about the drag dropping.

Thanks again

Frankie

Frankie

[AliGW]

Given the changes you want to continue to make, VBA might be the better option here. Someone will be able to suggest some code.

[Frankie_The_Flyer]

I’ll post on the vba pages thanks Ali

[AliGW]

No - I'll move this thread. As you know, duplicate queries are not allowed.

[kvsrinivasamurthy]

Here is the VBA code for worksheet event for "Test 1" sheet.
Drag and drop will not effect the CF. The code takes care of it.

Worksheetevent

To paste the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.

Please Login or Register  to view this content.

[Frankie_The_Flyer]

Wow! Quick response! Thanks kvsrinivasamurthy. Much appreciated.

[kvsrinivasamurthy]

Pl mark the thread solved.