|
Highlight Cells with Text or Formulas (non-empty cells)
This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, numbers, or other characters it will be filled in with color, or highlighted. The first macro listed will work on the entire active sheet which you are on. The second macro will be limited to a predefined or specified range in the worksheet; this range is determined within the macro and can be changed by changing the cell references on this line of code: For Each Rng In Range("A1:B25").
When you run this macro, it will overwrite all cells with color which currently contain data. This means that if you have a colorful worksheet and you run this macro, it will replace this color with the color specified in the macro.
This macro works best when you just want to highlight everything in a worksheet so you can more easily locate information. This is particularly useful if someone has hidden data by making the text the same color as the backgroun; using this macro in that case will color the background of all previously seemingly empty cells.
To change the color of the highlight for the first macro, change the number in this line of code If r.Value <> "" Then r.Interior.ColorIndex = 3 'red.
To change the color of the highlight for the second macro, change the number in this line of code Rng.Interior.ColorIndex = 3 'red.
Where to install the macro: Module
Highlight Cells with Text or Formulas (non-empty cells)
Works on the entire active worksheet
Sub Highlight_Cells_With_Text_or_Formulas()
'Highlights all cells with text or formulas on the active sheet
'Will remove color from cells without formulas or text
Dim r As Range
With ActiveSheet.UsedRange
.Interior.ColorIndex = xlNone
For Each r In .Cells
If r.Value <> "" Then r.Interior.ColorIndex = 3 ‘red
Next
End With
End Sub
Highlight Cells with Text or Formulas (non-empty cells)
Works on predefined range of cells.
Sub Highlight_Cells_With_Text_or_Formulas_Range()
'Highlights all cells with text or formulas on the active sheet
'Will remove color from cells without formulas or text
Dim Rng As Range
For Each Rng In Range("A1:B25") 'Range to highlight cells
If Rng.Value <> "" Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = 0 'blank
End If
Next Rng
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Dear All,
I'm begginer excel user. I would like to lock not empty cells because my sheet will be use by several users...I would like shield date after saved changes (when somebody fill each row and confirm). and I would like to set one user who could edit all dates... It is possible? Could you help me?( I have excel 2007.)
Hi! I hope someone can help me with this problem - I am going nuts over it, but I am sure there is a macro that could achieve this:
I need to delete empty cells within a sheet, row by row, until I hit cells in that row that are not empty. For example:
A B C D E F
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3
4 4 4 4 4 4
5 5
6 6 6 6 6 6
I want to select the first cell in column A, and then start running the macro. The macro should detect that row one should be left untouched, and proceed to row 2, than to row 3, and delete cells there, then proceed...
However, after cells have been deleted and a nonempty cell is hit, it should not delete empty cells after that. So, the above example should end up looking like this:
A B C D E F
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3
4 4 4 4 4 4
5 5
6 6 6 6 6 6
This is for a non-commercial purpose - and I hope one of you could share their insight. It would save me weeks of work (I am talking tens of thousands of rows...).
Thanks so much!
as
Hi everyone and thanks for any help.
I am trying to format a report when I have a country name, their information, the total, empty row next country name etc:
America
Hot dogs $400
Buns $500
Total $900
Bejing
Chicken $600
Sauce $800
Total $1400
Thatis as an example. Right now I have the first country how I want it. I copy and paste from a data table and delete all rows that sum = 0.
However when I do it for the second country it deletes my empty rows and puts the total at the top. If I were to do:
For lngLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 24 Step -1
If Evaluate("Sum(C" & lngLastRow & ":Z" & lngLastRow & ")") = 0 Then
Rows(lngLastRow).EntireRow.Delete
'Rows(lngLastRow).EntireRow.Hidden = True
End If
Then it would work. But the data is always changing so I can't permanetly code it to Row 24. But if there was a way I could search until the empty row above or until the country name? I know this is very confusing but any help would be appreciated. I've been working on this for dayss...
From a column of data, I'd like to have items that have hard-coded entries colored differently from items that were computed from formulas.
How do I do it (preferably in an automated way, so that if a cell in the range changes from one category to the other, its formatting will also change)?
Thanks!
Hi,
Thanks for all your help in the past.
I seeked help yesterday with the problem and i was provided a correct solution instantly. Thanks for the same.
However I think there is some problem with the code as the formula is only working in the active sheet and not on all the sheets.
I want the code to be running in all the sheets.
basically i want to highlight all the cells containing any formulas , except those which are already marked in yellow.
Code:
Sub blue()
Dim sht As Worksheet
For Each sht In Sheets
On Error Resume Next
sht.Cells.SpecialCells(xlFormulas).Select
For Each cl In Selection
If cl.Interior.ColorIndex <> 6 Then cl.Interior.ColorIndex = 4
Next cl
Next sht
End Sub
Please help me.
Thanks a lot.
can i use conditional formatting to highlight cells that have a formula in, and not those that are just typed.
i want to be able to highlight a cell that has NOT had the value overtyped.
any suggestions?
I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?
Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?
I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.
If I use functions like FIND, they look at the result of a formula, and not the formula itself.
I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.
Hi to all
I want code which will, on some command (ex. double click) highlight all cells in range if they have same text as selected cell.
Ok, I assume that's easy, but problem is, I have merged cells and by my small experience with VBA, I know that codes doesn't work properly with merged cells.
Any help?
Thanks
I am using the below code for a worksheet, how can i get it to apply to the whole workbook?
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim FirstAddress As String
Static ColIdx
Cancel = True
If IsEmpty(ColIdx) Then ColIdx = 3 Else ColIdx = ColIdx + 1
If ColIdx > 16 Then ColIdx = 3
ActiveCell.Interior.ColorIndex = ColIdx
With Range("A5:R55")
Set c = .Find(ActiveCell.value, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = ColIdx
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address FirstAddress
End If
End With
End Sub
|
|
