# Complete Rows Highlighted when there is specific data in cell with specific color & Latest Balance Get Auto Color

#### bobby786

##### Board Regular
Dear All experts ,

Please forgive me for my bad English as i am not native English guy. I am assigned to carry on some work from my company and i was given a excel sheet which was probably made by my previous colleague , since i am not very familiar with VBA for excel so i am looking for help from your guys.

Problem # 1- In sample Worksheet i have sheet Work and Manual , where as Manual sheet i just want to show how i am doing it manually , in which Col B "BANK NAME" is listing all the banks and it will show the running balance of that bank , but as you can see i have multiple banks so i have to assign each bank a color ( i want every time a new bank name is entered it will get a color highlight for whole row , right now i am doing it manually which is eating a lot of time and often i make mistakes in that. So it is possible in excel world to make it automatic.

Problem # 2 - Col H There is formula in place to show the latest balance of that particular bank and gets it highlighted with Yellow , so in order to find the last balance of each bank i can just look for this yellow color , but somehow it is not working if someone can help me get it fixed. P.S again the formula was not done by me.

I am trying to learn the great world of excel and looking forward for help/guide in my journey

EDIT: i have also attached the excel file here as attachment in case someone does not like to click on link.

Regards
Bobby

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### DanteAmor

##### Well-known Member
Run the following macro.
In column B the macro highlights in yellow the last balance of each bank. That way you can filter in column B by color.

VBA Code:
``````Sub lastest_balance()
Dim r As Range
Dim lr As Long, i As Long
Dim dic As Object, itm As Variant

lr = Range("B" & Rows.Count).End(3).Row
Set r = Range("B" & lr)
Set dic = CreateObject("Scripting.Dictionary")

Range("B:B").Interior.ColorIndex = xlNone

For i = 4 To lr
If Range("B" & i).Value <> "" Then dic(Range("B" & i).Value) = i
Next

For Each itm In dic.items
Set r = Union(r, Range("B" & itm))
Next

r.Interior.ColorIndex = 6
End Sub``````

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (lastest_balance) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

#### Fluff

##### MrExcel MVP, Moderator
Cross posted Highlights Rows when there is specific data in cell & Latest Balance Get Auto Color

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

#### bobby786

##### Board Regular
@DanteAmor
Thank you for your reply. Is there any method where i dont have to run the macro you provided every time when there is new/edit entries?

and Problem# 1 is not possible in excel ?

Regards

Run the following macro.
In column B the macro highlights in yellow the last balance of each bank. That way you can filter in column B by color.

VBA Code:
``````Sub lastest_balance()
Dim r As Range
Dim lr As Long, i As Long
Dim dic As Object, itm As Variant

lr = Range("B" & Rows.Count).End(3).Row
Set r = Range("B" & lr)
Set dic = CreateObject("Scripting.Dictionary")

Range("B:B").Interior.ColorIndex = xlNone

For i = 4 To lr
If Range("B" & i).Value <> "" Then dic(Range("B" & i).Value) = i
Next

For Each itm In dic.items
Set r = Union(r, Range("B" & itm))
Next

r.Interior.ColorIndex = 6
End Sub``````

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (lastest_balance) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

?

Last edited:

#### bobby786

##### Board Regular

Cross posted Highlights Rows when there is specific data in cell & Latest Balance Get Auto Color

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
i am very sorry for this , i was not aware of it , it wont happen next time.

Thanks

#### DanteAmor

##### Well-known Member
@DanteAmor
Thank you for your reply. Is there any method where i dont have to run the macro you provided every time when there is new/edit entries?
and Problem# 1 is not possible in excel ?

With conditional formatting.
- Select column B.
- Menú: Conditional Formatting
- New rule
- Formula:

=COUNTIF(B1:\$B\$1000000,B1)=1

- Format, select yellow color.
- Ok, Ok.

#### bobby786

##### Board Regular
With conditional formatting.
- Select column B.
- Menú: Conditional Formatting
- New rule
- Formula:

=COUNTIF(B1:\$B\$1000000,B1)=1

- Format, select yellow color.
- Ok, Ok.
Thank you very much it does solve my Problem # 2 ,
now i want to apply the same theory on whole ROW to get my Problem #1 fixed as well , can you tell me how to achieve that ? but keeping the Col B conditional formatting intact , whole ROWS will get its corresponding bank color , like say Bank1=Blue but Col B of that Bank1 if its latest balance still should get the Yellow otherwise the whole row is Blue color , Bank2 represent Orange color and so on..

Regards

Replies
8
Views
63
Replies
0
Views
180
Replies
2
Views
364
Replies
5
Views
272
Replies
13
Views
328

1,129,510
Messages
5,636,742
Members
416,938
Latest member
sc58963

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back