Free Microsoft Excel 2013 Quick Reference

Highlighting entire row using VBA while referencing cell that contains function

I am a novice Excel user who needs help with VBA code. I have researched my problem extensively, and have decided to post here as a last resort.

Basically what I need to be able to do is as follows:

If the number in a cell in column E, "risk rating," is within a certain range (total of 5 different ranges) I want the entire row over the range of columns (A-R) to be highlighted a certain color (depending on the range).

I know I can do this with conditional formatting, but only for 3 conditions. I found some code on the internet that will highlight the cell, but not the entire row. Also, this code only works if I type in the numerical value into the cell in column E. It WILL NOT work if the value in column E is the product of the numerical values in columns C and D. Unfortunately, this function must stay.

Therefore, basically I need code that will be able to evaluate the values in cells in column E (which contains the function "=D(row) * C(row)"), and depending on the range that the value in the column E cell falls, highlight the entire row (from column A - R) a certain color. If the cell value in column E is zero, the cell should be left as is (no highlighting).

I will find the colors myself using a macro. The 5 ranges I need are:
1-5, 6-10, 11-15, 16-20, and 21-25.

Please help!!!

Justin Swanson

Post your answer or comment

comments powered by Disqus
Can anybody help me to fix this macro i have it to where is highlighting a single cell i will like the macro to highlight entire row base on a single cell value

thankyou for any help provided
Sub colorentirerow()
'base on a value of single cell highlight entire row
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
For Each mycell In Selection
If mycell.Value Like "5" Then
mycell.Interior.ColorIndex = 3
End If
End Sub

I am looking to select entire rows (Using code to go into a macro) based on partial contents of a cell. In the attached spreadsheet example I need to select the highlighted area. This is based on the first 4 characters in column A. i.e. the range is column A with the characters AB11 TO AB92. I have been asssuming that I need to use the "MID" function in some way but have failed miserably with each attempt I have made with creating the code (Probaly due to my lack of VBA skills and understanding of the MID function).
If anyone could give some pointers it would be appreciated.

(P.S. I asked a similar question in a different thread but over complicated it by putting in the next stage. I marked that thread as "SOLVED" and put in a comment to say I was going to start a new thread. Please advise if that is the correct way of handling it or should I have done it another way i.e. ask a moderator to close/delete the thread etc)

hi again... Can somebody help me build a code that will highlight a entire row when a cell from a column has a value of 0. It will check all the cell from Column C1:C100 and check all the cells that contains a 0 value. Need to highlight the entire row that contains this cell value and fill it in orange color. Thanks in advance.

Friends and Experts,

My colleguaes and I work with a rather large data entry/archiving Excel 2007 spreadsheet on a daily basis. It contains several columns and rows that will not easily fit on one viewable screen. We have searched for the ability to highlight the entire data set across the row and down the column of the selected cell. This would allow us to navigate with the directional keys quickly and enter data assuring us we are on the correct row and column with a simple visual indication.

I know that the number and letter of the row and column highlight on their own, but what we are needing is for the entire row and column of data to highlight as well.

I have attached an example of what we would like to see.

Does anyone know of a setting/function/method to accomplish this? I have searched around to no avail.

I have a big options sheet, I want to arrange according header row using vba.
I arrange all sheet this type

Size color Finish Color (Header Row)
1. Size color Finish Color
2. Size color Finish Color
3. Size color Finish

If not match header then cell is blank.

Please Check attachment,

I know there is a way to highlight and select an entire row or an entire column, for example if you wanted to search just a row or column for something. But is there a setting that highlights the row and column of the cell that you are currently selecting ? Basically I'm looking for a better way to identify which row and column I'm in other than just highlighting the column and row name which is what it currently does by default.

hi all
I am very new user to Excel and not too familiar with VBA... I try to create some work sheet base on this topic but I still have problem I can not highlight entire row after row 23 until 70 also the column(COLUMN N) that has check box doesn't change colur as well.
I appreciate any help anyone is able to provide in solving this tedious task. I've uploaded what I'm working with as well...


I have a big options sheet, I want to arrange according header row using vba.
I arrange all sheet this type

Size color Finish Color (Header Row)
1. Size color Finish Color
2. Size color Finish Color
3. Size color Finish

If not match header then cell is blank.


I need to highlight entire rows based on the duplicates in one column.

Please Help.


I would like to insert a row using VBA when user close the the spreadsheet.
Is it possible to do so?
Can anyone gives me example code?
Your help is great appreicated.



I want to highlight entire row if the columns from "E" to "L" does not cotain string like gaurantee


is there anyway to create a row using VBA and insert it somewhere?? if so then how?

How someone can help

I know you can change a colour of a cell using a condition, But what im trying to do is colour a entire row or the number of cells that make up that row, depending on a condition of a formula in a particular row.

example, a sheet with 5 columns A B C D E, is the answer in cell E1 is false then colour that row i.e A1, B1, C1, D1 and E1 etc

How this explains what im trying todo


Ladies and Gentlemen,

I have a bit of a problem with conditional formatting. I've looked around for quite a bit and haven't found a solution.

I would like to highlight entire rows based on the values in column C. This is fairly straight forward for greater than, less than, or equal functions. How would I go about achieving this for a between function?

08:00:00 > x < 16:00:00

I am evaluating a time sheet which is linked to our electronic entry system and need to highlight employees entering after the beginning of the work day or leaving before the end of the work day.



Is it possible for the excel to automatic highlight entire rows if the row has a day that falls on a saturday or sunday?

For the life of me, I can not figure this out.

I have attached a example workbook to illustrate my problem.

I have mutiple cells with my workbook that contain long lines of text. This excel document is more like a word document, but is pre-populated with other cells located on various worksheets located within the same workbook. Due to the fact that is like a word document, the end of text line within the cell is "wrapped" down to the next line and continues one. Very simple - just like writing in a word doc.

But I want to auto resize the height of that cell - and when you do - it resizes it to one line (row height = 16.5) and hides all the text within that cell and therefore doesnt show when printed.

Now I know I can manually drag the height to show what I want - but with 500+ lines of text within my worksheet, I do not want to do that each time the "report" is generated.

I have tried various types of combinatious of merged cells, unmerged cells, center across selection....and I cant seem to get it.

Any suggestions?


I have two rows of data. In row 1, I have year1, year2, year3 etc. Directly underneath in row 2, some cells contain numbers and some contain N/A. What I want to do is create a macro that will look up the values in row B, select only those cells that contain numbers and set the range equal to x. Then I want it to select the cells in row A that correspond to the cells in row B that contain numbers, and set this range equal to y. This is so I can make a line graph of x against y. Does anyone have any idea on how to do this? I've attached a workbook with some numbers if it makes things clearer.


Hey guys,

I have some data that I exported and I have excel parsing it. I am using Text2Columns, but what happens is some employees have there first, middle, and last name in the report. This makes everything on that row shift over by 1 cell to the right. I am wanting to have excel look at one column, find the cells that contain text (A-Z, a-z, etc), delete those cells and shift the cells left. Is that possible?

This forum has been critical to me getting a HUGE project completed for my job. I would like to think everyone who has helped me so far. Thank you!

I need some help..I want to delete a cell that contains no value in the next row


1 Apple 2
2 Banana
3 Grapes 5
4 Tomato

In the sample above, i want to delete A2 and A4 since it contains no value in column B


I'm looking for a solution to this ! Will be very happy if you guys can sort this out !

As the title says, i want to replace a word/string from a cell that contain a string from another cell!


Column A contains many words including [img:1orcqvu5] and column B contains only : 1orcqvu5

Next Row - Column A contains many words including [img2d63ybp] and column B contains only : x2d63ybp

And so on ....

Is there any program by which i can replace

all the [img:1orcqvu5] /[img2d63ybp] / .... /..... / so on from column A to just [img] or to something else !

I have attached a sample file, will be grateful if somebody can solve this issue !



I have a sheet with a list of items. One column has a description and I need
to find all the description cells that contain "North America" for example.
What can I use.

Thanks and Regards


Is there a way to count cells that contain formulas, but only those cells
that return a value. I tried using COUNTA...but that sees the ceel as
nonblank also.

In Excel, I need to protect cells that contain formulas but for thousands of
files and sheets. The real trick is being able to go back into these files
and allow macros to operate properly while under protection. Any suggestions?

I have set up a table in which I need to average only the cells that contain
numbers and I need to ignore cells that contain #DIV/0 from a formula that I
have entered without changing them to zero. I still need the formula for the
other cells. I need to setup a formula that is consistent for workbook so I
will need to be able to include the #DIV/0 cells in my formula but still
average only the cells that have a number in them. I have tried the Count
function but I need it to recognize the numbers and average them while
ignoring #DIV/0.

No luck finding an answer? You could always try Google.