2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Trying to conditional format based on an index

Hey guys, trying to make my life easier, after an hour of testing out a few different things, still can't get this to work.

What I'm attempting to do is create a sheet to automatically highlight one of 3 different rows via conditional formatting, based on what I Input, separated by 2 sheets.

Sheet 1 column C is the input (10, 20, and 30), which will highlight a cell in Sheet 2 column K, L, or M (Each column relates to 10, 20, and 30)
Sheet 1 Column C needs to reference Sheet 1 Column A, then index match against Sheet 2 Column E, which then calls back to highlight K, L, or M based on whether C is 10, 20, or 30.

Basically Check what 1A matches to in 1C, then Match 1A to 2E, highlight 2K, 2L, or 2M based on what's in 1C.

Direct Example

Sheet 1 A1 = 12/12
Sheet 1 C1 = 10

Sheet 2 B22 = 12/24 ---- Sheet 2 K22 (20) = 24

Sheet 2 B23 = 12/12 ---- Sheet 2 K23 (10) = 23

I want K23 to highlight because 1A is triggering on B23 and has 10 put into C1.

I saw that there's work arounds to splitting the two sheets, but I'm not adverse to putting a table into a single sheet if that is the path of least resistance. I can utilize Columns O and Q instead 1A and 1C.

I just cannot figure out the right code, since I know it's gotta be a custom one.

submitted by /u/KingScuba
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#rows.com
#conditional formatting
#index match
#Excel
#sheets
#columns
#rows
#highlight
#input
#reference
#table
#code
#custom formula
#A1
#C1
#K23
#B23
#formula
#matching
#10, 20, 30