Need help converting coloring macro

ThatOneGuy_1337

New Member
Joined
Apr 11, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi all,

I have a macro that works but I need to convert it and my VBA knowledge isn't good enough to do this myself.
Long story short it colors cells based on colors in a different sheet.

Currently vertical coloring is working:
(It looks for the value on top, and then proceeds to color the amount of blocks based on the amount of times it finds that value in a different sheet)
1649656947931.png



Now I would need the macro to be able to color horizontal as well:
1649657032857.png



This is the code that is coloring the cells:
VBA Code:
'Coloring
For Each c3 In ws.Range(ws.Cells(BeginRow, kolominws), ws.Cells(EndRow, kolominws))
If c3.Interior.Color = 16777215 And c3.MergeCells = False Then
c3.Interior.Color = kleur
c.Interior.Color = kleur
Exit For
End If
Next c3
Next c

It gets the values from a vlookup in a sheet where the locations of the cells are given
VBA Code:
BeginRow = Application.WorksheetFunction.VLookup(blok, rangews.Range("A:G"), 6, False)
EndRow = Application.WorksheetFunction.VLookup(blok, rangews.Range("A:G"), 7, False)
BeginColumn = Application.WorksheetFunction.VLookup(blok, rangews.Range("A:H"), 8, False)

If anybody could help converting this code to work vertically it would be a huge help! I can provide a test file if needed to see how it all works.
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,216,728
Messages
6,132,370
Members
449,721
Latest member
tcheretakis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top