High light cells or conditional formatting

  • Thread starter Thread starter jenna
  • Start date Start date
J

jenna

Hello everyone,
I am not sure if conditional formatting could be used in
this case. I am working on a data that I will like for
the rows across to high light if only all the cells
across have some value. This could include text. However,
there should be some data across each cell in the rows. I
am planning to add this to a macro. Egg.
A B C D
Sales 50 10 11
Sales 10
50 5 30 25
6 11 25
Answer.Only these rows will be high lighted.
A B C D
Sales 50 10 11
50 5 30 25.

Any help will be great. Thanks in advance.
 
use conditional formatting

=countA($A1:$D1)=4

as an example of the formula to use for cells A1:D1
 
Jenna

You do need a macro - try this

Sub hiliteRows()
'Assumes data bigins in A1
Dim nr As Long, nc As Integer, count As Integer
Dim i As Integer, j As Integer
Dim rng As Range
Dim fill As Boolean
Cells.ClearFormats
Range("A1").Select
nr = ActiveCell.CurrentRegion.Rows.count
nc = ActiveCell.CurrentRegion.Columns.count
For i = 1 To nr
For j = 1 To nr
If Not IsEmpty(Cells(i, j)) Then
count = count + 1
If count = nc Then
Range(Cells(i, 1), Cells(i,
j)).Interior.ColorIndex = "6"
End If
End If
Next j
count = 0
Next i

End Sub

Regards
Peter
 
use conditional formatting

=countA($A1:$D1)=4

as an example of the formula to use for cells A1:D1
 
If you're looking just in columns A:D, you can use this Conditional
Formatting:

Select Columns A:D. Choose Format/Conditional Formatting and enter

Formula Is =COUNTA($A1:$D1)=4

and select your highlight color.

If you can have variable number of columns filed (up to a max of 255),
but want to highlight those in which all data is contiguous, you could
Select columns A:IU and use this CF:

Formula Is =AND(COUNTA($A1:$IU1)>1,SUMPRODUCT(--($A1:$IU1<>""),
2^(COLUMN(INDIRECT("A:IU"))-1))=(2^COUNTA($A1:$IU1)-1))

This can be slow, but reducing the number of columns will speed it up.
Say you have a maximum of 20 columns of data. In that case, Select
columns A:T and use CF:

Formula Is =AND(COUNTA($A1:$T1)>1,SUMPRODUCT(--($A1:$T1<>""),
2^(COLUMN(INDIRECT("A:T"))-1))=(2^COUNTA($A1:$T1)-1))
 
Your looping from 1 to nr in both i and j
doubt that is what you intended. Maybe you need to clean it up and repost.

Also, it seems premature to make a decision within your j loop on each
iteration. Obviously, it can't pass until nc is reached.
 
Back
Top