VBA or Macro - apply formatting with three levels of conditions

T

techgirl

Board Regular
Joined
Sep 16, 2002
Messages
178
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I need to set up a worksheet that will search for a department using the Command Button. Once it finds that department name, then look at the number and see change color according to the three levels..."target" value, Middle Value, Below Value. Each of the three scale values will have a different color.

Example would be...
User enters the department they are searching for by using the command button, value goes to B2. Now B2 is "Lab" Search. Somehow compare B2 and find Lab in the worksheet(it is in B21, then look in B22 where the value is. Now determine where the number falls in the value scale (Target, Middle, Below). Then color with appropriate scale colors.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
I was wondering if anyone has any comments or solutions for my question??
 
Upvote 0
A little clarification is needed. You want the user to be prompted to enter a search value and that value to be written to cell B2. Then you want to find that value in the worksheet. Is this correct? Can that value be anywhere in the worksheet or is it in any specific column or range? What are the value scales for Target, Middle and Below?
 
Upvote 0
Example, I just want to find a department ("Lab"), then the data in the range below that title "Lab", automatically look at the data, apply color according to it the numbers are at the target range or below.

Whatever is easiest for the user to find that specific department they want at the particular time. Then let excel automatically apply formatting for them.
 
Upvote 0
Are the department names in the first row of each column? In order to apply color according to the target value, I need to know where that target value is located. Is it located in the cell below the department name? It is very hard to offer a solution without a clear understanding of how you data is organized. If possible, perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and ranges.
 
Upvote 0
Example: A3: Department; B3 "Lab"; B4 55%, C4 75%, D4 90%. B4 would be Red because its the low value, C4 would be yellow because its the middle value, D4 would be green because it hit the target value. I will see if I can upload the document to www.box.com.
 
Upvote 0
I may have missed something here, but why not just apply CF the ALL the cells.
They will then change colour as data is entered
 
Upvote 0
I have tried. I can do it if the CF for numbers. But she has percentages, so I am trying to use the "Format all cells based on their values" and for some reason the CF won't color code each color, it just does all the numbers for one color, even though I have three rules applied.
 
Upvote 0
post a sample of your CF inputs
usually with percentages users get caught up with inputting, for example "if its less than" 85 %....it may be better inserted as "if it's less then" 0.85
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
VBA Apply Formatting Based on 2 Criteria and If Another Cell Contains...
2
Replies
13
Views
210
Joe4
Joe4
D
  • Question
Auto-Update Problem
Replies
0
Views
252
Diulama1023
D
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
181
rollis13
rollis13
G
  • Solved
History of Dates to be traced in Excel (2010) in DD/MM/YYYY format
2
Replies
11
Views
357
MARK858
MARK858
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
258
Gajendran Yadhav
G

Forum statistics

Threads
1,214,588
Messages
6,120,409
Members
448,959
Latest member
camelliaCase

Share this page

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