Conditional formatting very complex....

[ExcelNovice]

Hi all,

I need a conditional formatting code that will remove the grid lines from each cell in the range H2 to J45 if the cell is empty. If the cell is not empty, the conditional formatting should provide border outline for each cell with content. Finally the conditional formatting should color each row that is not empty with an alternate color blue then red.

Hope this makes sense....thanks.

 

[lenze]

You can do either of your conditions. but not both with conditional formatting. The reason is, if the first condition is true, Excel will not check for the second condition.

My suggestion would be to set the row colors using condtional formatting and the Worksheet Change Event to place a border around cells when data is entered.

HTH

lenze

On second thought, you MIGHT be able to write a complex AND/OR condition that would handle this, but I'm not sure.

 

[Krishnakumar]

May be..

Select the range and in CF,

Condition 1

Formula Is:

=A1=""

Click the format and clear the border

Condition 2

Formula Is:

=AND(A1<>"",MOD(ROW(A1),2)=0)

Format the border and in Patterns select the Blue color

Condition 3

Formula Is:

=AND(A1<>"",MOD(ROW(A1),2)<>0)

Format the border and in Patterns select the Red color

HTH

 

[Peter_SSs]

ExcelNovice

Or possibly... Remove all borders from the range, then just use Kris' Conditions 2 & 3 as your Conditions 1 & 2.

Note that when selecting H2:J45 and applying Kris' formulas, you would need to substitute H2 where he has A1

 

[lenze]

You guys are getting close, but the above will NOT necessarily alternate colors. If the OP has data in an even row (say row 4) and row 5 is blank, and then data again in row 6, both rows 4 & 6 will be colored the same.

lenze

 

[Peter_SSs]

You guys are getting close, but the above will NOT necessarily alternate colors. If the OP has data in an even row (say row 4) and row 5 is blank, and then data again in row 6, both rows 4 & 6 will be colored the same.

lenze

Absolutely right lenze. Also, our suggestions only colour the CELL if it contains data, not the ROW.

All that I think means that this cannot be done with traditional conditional formatting. Always dangerous to make such a statement, I know. My reason is, there are 5 possible cell formattings required.

1. No colour, no border: If row contains no data.
2. Red & border: If cell contains data and previous data row was blue.
3. Red and no border: If cell does not contain data but there is data elsewhere in the row and previous data row was blue.
4. Same as 2 but with red/blue swapped.
5. Same as 3 but with red/blue swapped.

 

[lenze]

How important are the borders? I can do the other parts, but the borders around cells with data will require coding. How is the data entered into the cells?? If manually entered, we can use a WorkSheet Change Event for the borders, and CF for the rest.

lenze