I've got a table with salary information named SalaryDataTbl, and cell K2 to the right of it has a picker where you may choose an employee. L2 checks their discipline while M2 checks their pay.

Is there a way to use an in-cell function or data validation to emphasize the SalaryDataTbl's nearest salary for their discipline? Or will a VBA solution be needed for this?

It should highlight cell G2 in the example in the image for the salary of 3750 and the discipline of programming.

Feb 18, 2023 in Others 149 views

## 1 answer to this question.

Conditional formatting will be used to highlight cells based on the values of other cells. You'll need Excel 2021 or Excel for Office 365 for this response. Under the home tab, click "Conditional Formatting" > "New Rule" and change the setting to "Use a formula to choose which cells to format" after selecting the range D2:I18.

The formula you need to use will be

```=AND(ABS(D2-\$M\$2)=MIN(ABS(DROP(D:I, 1)-\$M\$2)), \$A2=\$L\$2)
```

Then, using the formatting options, choose the format you need to "Highlight" the pertinent cells, such as changing the fill colour to yellow.

• 63,420 points

## Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

## Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

## Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

## VBA - user input of row value to use in a cell range

Use this: Range("C" & c & ":I" & ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE