Highlight row if condition is met

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day all;

I am not sure if conditional formatting can do this but I want to select my
entire sheet and state that;

IF any cell in Column X is >=30 then highlight corresponding Row (yellow)

Can this be done?

Thanks in advance
 
Save a copy of your workbook and in the back up copy see if the following
code does the trick for you:
*************************************************
Sub HighlightRow()

Dim dblVal As Double
Dim lngRowOffset As Long
Dim strCurrentRow As String
Range("X1").Select
dblVal = ActiveCell.Value

Do While dblVal <> 0
strCurrentRow = CStr(lngRowOffset + 1)
If dblVal > 30 Then Rows(strCurrentRow).Interior.Color = vbYellow
lngRowOffset = lngRowOffset + 1
dblVal = ActiveCell.Offset(lngRowOffset).Value
Loop

End Sub
*************************************************
 
In the workbook you want to do the highlighting in press Alt + F11 to open
the Visual Basic Editor. In the VBE menu, click INSERT and select MODULE.

In your newly minted module, paste the text that is between the line of
asterisks.

To execute the code directly from the module, press F5. To run it from the
workbook press Alt + F8, select the macro named HighlightRow and click the
RUN command button on the right side of the dialog box.

The macro assumes that the active sheet is the sheet you wish the
highlighting to be done in.
 
Maybe conditional formatting:-

Select all cells by cliking the block above the 1 of row 1 and then

Format|Conditional format

Formula is

and enter the formula =$X1>=30

and choose you colour.

Mike
 
Hey Kevin, I tried this (and maybe I did it wrong) and it turned half of the
rows (up to 35 even though values in the row had 30 - 34 as well) and all my
column headers and title yellow, plus it seem to of skipped every 2nd row

This is what I put in

Sub HighlightRow()

Dim dblVal As Double
Dim lngRowOffset As Long
Dim strCurrentRow As String
Range("A4:A120").Select <<Neall ---- This is the Column with the Values I
want to have evaluated --->>
dblVal = ActiveCell.Value

Do While dblVal <> 0
strCurrentRow = CStr(lngRowOffset + 1)
If dblVal = 30 Then Rows(strCurrentRow).Interior.Color = vbBlue
lngRowOffset = lngRowOffset + 1
dblVal = ActiveCell.Offset(lngRowOffset).Value
Loop

End Sub

Any suggestions, Thanks in advance
 
Don't select the entire column, just select A4. The lngRowOffset variable
will count off the rows, incrementing by one each time a cell is evaluated.
Change Range("A4:A120").Select
to
Range("A4").Select
 
Ok did that and whats happening now is its highlighting all rows above A4
which are my column headers and title.
 
SEe if this works for you:
******************************************************
Sub HighlighRowII()

Dim dblVal As Double
Dim lngRowOffset As Long
Dim strCurrentRow As String
Dim l As Long

Range("A4").Select
dblVal = ActiveCell.Value

For l = 4 To 120
dblVal = Range("A" & l).Value
If dblVal = 30 Then Rows(CStr(l)).Interior.Color = vbBlue
Next l

End Sub
******************************************************
 
Hey again Kevin, it worked for only 1 row it was wierd because I had put a
value in an empty row (for testing) within the range when I used >= 30 the
row that was changed was 40 even thought within the 'A' column there are
values between 1 and 237.

Thanks again for your help on this
 
Kevin I have found the error in my ways, I had a lingering old conditional
format on one of the rows, once I removed it this worked!!!

Thanks very much appreciate all your time
 
1 more question if I may, how do I get the macro to run apon refresh so I
dont have to manually execute the macro after the data is retrieved from my
SQL query?
 
You can run this macro by going to the Tools menu ----> Macros -----> Macros (has a play symbol next to it). This will bring up a list of those macros that you have built and you can select the appropriate one to run... (Shortcut key to this window = F8)

=?Utf-8?B?TmVhbGw=?= said:
1 more question if I may, how do I get the macro to run apon refresh so I
dont have to manually execute the macro after the data is retrieved from my
SQL query?
--
Neall


"Neall" wrote:

> Hey again Kevin, it worked for only 1 row it was wierd because I had put a
> value in an empty row (for testing) within the range when I used >= 30 the
> row that was changed was 40 even thought within the 'A' column there are
> values between 1 and 237.
>
> Thanks again for your help on this
>
>
> --
> Neall
>
>
> "Kevin B" wrote:
>
> > SEe if this works for you:
> > ******************************************************
> > Sub HighlighRowII()
> >
> > Dim dblVal As Double
> > Dim lngRowOffset As Long
> > Dim strCurrentRow As String
> > Dim l As Long
> >
> > Range("A4").Select
> > dblVal = ActiveCell.Value
> >
> > For l = 4 To 120
> > dblVal = Range("A" & l).Value
> > If dblVal = 30 Then Rows(CStr(l)).Interior.Color = vbBlue
> > Next l
> >
> > End Sub
> > ******************************************************
> > --
> > Kevin Backmann
> >
> >
> > "Neall" wrote:
> >
> > > Ok did that and whats happening now is its highlighting all rows above A4
> > > which are my column headers and title.
> > >
> > >
> > > --
> > > Neall
> > >
> > >
> > > "Kevin B" wrote:
> > >
> > > > Don't select the entire column, just select A4. The lngRowOffset variable
> > > > will count off the rows, incrementing by one each time a cell is evaluated.
> > > > Change Range("A4:A120").Select
> > > > to
> > > > Range("A4").Select
> > > >
> > > > --
> > > > Kevin Backmann
> > > >
> > > >
> > > > "Neall" wrote:
> > > >
> > > > > Hey Kevin, I tried this (and maybe I did it wrong) and it turned half of the
> > > > > rows (up to 35 even though values in the row had 30 - 34 as well) and all my
> > > > > column headers and title yellow, plus it seem to of skipped every 2nd row
> > > > >
> > > > > This is what I put in
> > > > >
> > > > > Sub HighlightRow()
> > > > >
> > > > > Dim dblVal As Double
> > > > > Dim lngRowOffset As Long
> > > > > Dim strCurrentRow As String


> > > > > Range("A4:A120").Select < > > > > > want to have evaluated --->>
> > > > > dblVal = ActiveCell.Value
> > > > >
> > > > > Do While dblVal <> 0
> > > > > strCurrentRow = CStr(lngRowOffset + 1)
> > > > > If dblVal = 30 Then Rows(strCurrentRow).Interior.Color = vbBlue
> > > > > lngRowOffset = lngRowOffset + 1
> > > > > dblVal = ActiveCell.Offset(lngRowOffset).Value
> > > > > Loop
> > > > >
> > > > > End Sub
> > > > >
> > > > > Any suggestions, Thanks in advance
> > > > > --
> > > > > Neall
> > > > >
> > > > >
> > > > > "Kevin B" wrote:
> > > > >
> > > > > > In the workbook you want to do the highlighting in press Alt + F11 to open
> > > > > > the Visual Basic Editor. In the VBE menu, click INSERT and select MODULE.
> > > > > >
> > > > > > In your newly minted module, paste the text that is between the line of
> > > > > > asterisks.
> > > > > >
> > > > > > To execute the code directly from the module, press F5. To run it from the
> > > > > > workbook press Alt + F8, select the macro named HighlightRow and click the
> > > > > > RUN command button on the right side of the dialog box.
> > > > > >
> > > > > > The macro assumes that the active sheet is the sheet you wish the
> > > > > > highlighting to be done in.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Kevin Backmann
> > > > > >
> > > > > >
> > > > > > "Neall" wrote:
> > > > > >
> > > > > > > Is this something I can past right into the excel Formula bar or VB related?
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Neall
> > > > > > >
> > > > > > >
> > > > > > > "Kevin B" wrote:
> > > > > > >
> > > > > > > > Save a copy of your workbook and in the back up copy see if the following
> > > > > > > > code does the trick for you:
> > > > > > > > *************************************************
> > > > > > > > Sub HighlightRow()
> > > > > > > >
> > > > > > > > Dim dblVal As Double
> > > > > > > > Dim lngRowOffset As Long
> > > > > > > > Dim strCurrentRow As String
> > > > > > > > Range("X1").Select
> > > > > > > > dblVal = ActiveCell.Value
> > > > > > > >
> > > > > > > > Do While dblVal <> 0
> > > > > > > > strCurrentRow = CStr(lngRowOffset + 1)
> > > > > > > > If dblVal > 30 Then Rows(strCurrentRow).Interior.Color = vbYellow
> > > > > > > > lngRowOffset = lngRowOffset + 1
> > > > > > > > dblVal = ActiveCell.Offset(lngRowOffset).Value
> > > > > > > > Loop
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > > *************************************************
> > > > > > > > --
> > > > > > > > Kevin Backmann
> > > > > > > >
> > > > > > > >
> > > > > > > > "Neall" wrote:
> > > > > > > >
> > > > > > > > > Good day all;
> > > > > > > > >
> > > > > > > > > I am not sure if conditional formatting can do this but I want to select my
> > > > > > > > > entire sheet and state that;
> > > > > > > > >
> > > > > > > > > IF any cell in Column X is >=30 then highlight corresponding Row (yellow)
> > > > > > > > >
> > > > > > > > > Can this be done?
> > > > > > > > >
> > > > > > > > > Thanks in advance
> > > > > > > > > --
> > > > > > > > > Neall
> > > > > > > > >
Click to expand...
 
Last edited:
If you place the name of the macro in a worksheet module and select the
Worksheet_Change event the SQL update might trigger the event.

Press Alt + F11 to activate the VBE and you should see a project window on
the left side of the screen displaying assorted objects like. If the
Microsoft Excel object tree has a + to its left, click the + to expand the
tree. Double click on the sheet that receives the data. In the module
window to the left there are 2 drop down combo boxes, select Worksheet from
the one on the left and Change from the one on the right. If that doesn't do
it try changing the Change event to Calculate and see if that triggers the
macro appropriately.

Type the name of the macro you wish to execute upon change and it should
look similar to the following:

Private Sub Worksheet_Change(ByVal Target As Range)

YourMacroNameHere

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If Then or Case help 3
Conditional Formatting 1
One click row highlighting 11
highlight row if cell value equal zero 2
help with highlight code 4
A Huge Conditional Format Challenge.... 2
Highlight Row Based On Condition-So close 3
Conditional Formatting with VBA 4

Back
Top