Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
High light cells or conditional formatting
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
High light cells or conditional formatting
use conditional formatting
=countA($A1:$D1)=4 as an example of the formula to use for cells A1:D1 -- Regards, Tom Ogilvy jenna wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
High light cells or conditional formatting
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 -----Original Message----- 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
High light cells or conditional formatting
use conditional formatting
=countA($A1:$D1)=4 as an example of the formula to use for cells A1:D1 -- Regards, Tom Ogilvy jenna wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
High light cells or conditional formatting
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)) In article , "jenna" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
High light cells or conditional formatting
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. -- Regards, Tom Ogilvy Peter Atherton wrote in message ... 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 -----Original Message----- 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
High light data | Excel Discussion (Misc queries) | |||
how to high light cells | Excel Discussion (Misc queries) | |||
Is it possible to high light a row of cells | New Users to Excel | |||
Conditional Formating for dates beyond 30, 60, 90 to be high light | Excel Worksheet Functions | |||
high light row your working in | Excel Discussion (Misc queries) |